[파워피벗공부] 5. 테이블간 관계를 이용한 간단한 손익계산

지금까지 파워피벗 포스팅의 내용을 가지고 간단한 활용 예시를 만들어 보자. 

예시에 사용되는 재료 (Data) 는 

• 매출실적 데이터  (Sales data)
• 품목 마스터 데이터 (Product master)
• 매장 마스터 데이터 (Shop master)

이 3가지 데이터를 가지고 "품목 / 매장 별 매출액과 손익"을 파워 피벗으로 구성 해보자. 

[오늘의 데이터~]

작업 할 순서는 다음과 같다.

1. 각각의 테이블을 데이터 모델에 담는다. 
  - Power Pivot 리본 메뉴의 데이터 모델 추가 버튼을 이용

2. 각 테이블간의 관계를 만든다. 
  - 품목 마스터와 매출 데이터간 관계는 "product_code" 로 
  - 매장 마스터와 매출 데이터간 관계는 "shop_code" 로 구성 

3. 측정값을 만든다. 
  - 매출수량 , 매출액 (매출수량 * 판매가격) , 원가 (매출수량 * 매입가격 ) , 커미션금액 , 손익 (매출액  -  원가 - 매출 커미션) 

4. 피벗 테이블을 구성한다. 

---

2019/07/04 - [엑셀/파워피벗 연구] - [파워피벗 공부] 1. 데이터 모델 만들기

[파워피벗 공부] 1. 데이터 모델 만들기

2019/07/05 - [엑셀/파워피벗 연구] - [파워피벗공부] 2. 테이블 간 관계만들기

[파워피벗공부] 2. 테이블 간 관계만들기

1~2 번 작업내용은 지난 포스팅의 설명으로 대신 하고 바로 측정값 만들기 부터 시작 ~! 

3. 측정값 만들기 

[측정값은 데이터모델 창을 열지 않고 바로 만들 수 있다.]

필요한 측정 값은 일단 5개 ~
모든 측정값의 생성은 매출실적(fSales_data) 테이블에 한다. 

1) 매출수량
  -->  매출수량 :=sum(fSales_data[Sales Quantity])

2) 매출액 (매출수량 * 판매가격) 
  -->  매출액:=CALCULATE(fSales_data[매출수량]*SUM(fprod_master[sales_price]))

3) 매출원가 (매출수량 * 매입가격)
  -->  매출원가:=CALCULATE(fSales_data[매출수량]*SUM(fprod_master[purchase_price]))

4) 커미션금액 
  -->   커미션 금액:=SUM(fSales_data[커미션])

5) 손익 (매출액 - 원가 - 매출 커미션) 
  -->   손익:=CALCULATE(fSales_data[매출액]-fSales_data[매출원가]-fSales_data[커미션 금액])

* 커미션 금액은 커미션을 열추가로 만든 뒤 적용해야 '커미션 금액' 측정값 산정이 가능하다.
  (합계금액이 제대로 나온다)

4. 피벗 테이블 구성하기

측정값을 만들면 작업은 끝난것과 다름없다. 나머지는 피벗팅을 하면서 눈에 잘들어오는 형태로 구성만 하면 된다. 

[완성된 측정값 구성 결과]

fSales_data 테이블에 측정값이 완성되었다. 이제 피벗팅 ㄱㄱ ~!! 

[완성된 피벗테이블]

품목 / 매장 별 피벗 테이블이 완성되었다. 

이와 비슷한 수준의 데이터로 손익을 계산해서 분석 및 보고를 수행해야 한다면 매번 작업 할 필요 없이 매월 데이터만 표에 변경해주고 새로고침 한번이면 끝이다. 

과거 엑셀로 원가결산, 사업계획, 스팟성 보고서 를 만들어내기 위해 진행했던 작업들을 생각하면 쩝... 
시간이 아깝다. 

이제 더이상 이런 종류의 데이터 작업을 위한 Vlookup 안해도 된다. 데이터 모델만 잘 꾸려 놓으면 데이터 값만 복붙하여 일을 끝낼 수 있다. 

Data_품목_매장_매출실적_PP3_손익계산.xlsx

0.29MB

---

직접 본인의 데이터로 예시와 비슷한 데이터모델과 피벗테이블의 구성이 필요하신 분은 글 남겨주세요~
시간이 가용하면 도움을 드릴 수도 있겠네요.