Power Pivot · 데이터 모델 · DAX 기초 — 관계·측정값·CALCULATE·FILTER·SUMX 완전 가이드
이 글은 Excel 365 환경에서 직접 재현·검수한 절차를 기준으로 정리했으며, 초안 정리에 AI 도구를 보조적으로 활용했습니다.
선행 학습·연계 글
1. 스타 스키마 설계
사실(Fact): Sales(날짜, 품목ID, 고객ID, 수량, 금액). 차원(Dim): Products, Customers, Date 등. 키는 정수·고유값 권장.
- 숫자/날짜 형식 정규화는 Power Query에서 끝내기.
- 너무 넓은 사실 테이블은 CHOOSECOLS 아이디어로 슬림화.
샘플 데이터(CSV) — 복사해 사용
SalesDate,ProductID,CustomerID,Qty,Amount 2025-07-01,P001,C001,2,30000 2025-07-02,P002,C002,1,12000 2025-07-02,P001,C003,1,15000 2025-07-03,P003,C001,3,45000
2. 데이터 모델로 로드
- Power Query로 정리 → 홈 > 닫기 및 로드(대상: 데이터 모델).
- Power Pivot 창에서 테이블 보이기 확인.
3. 관계 설정과 필터 방향
일대다(1:*), 차원(1) → 사실(*) 단방향 필터가 기본. 다대다 필요 시 브릿지(매핑) 테이블을 고려.
- 중복 키·누락 키는 오류 방어 후 로드.
4. 측정값 vs 계산 열
측정값은 보고서에 따라 즉시 계산(필터 컨텍스트). 계산 열은 행당 미리 계산(행 컨텍스트). 집계는 대부분 측정값으로.
// 측정값 예시(모델에서 작성)
Total Sales := SUM(Sales[Amount])
Orders := DISTINCTCOUNT(Sales[SalesDate] & "|" & Sales[CustomerID] & "|" & Sales[ProductID])5. 핵심 DAX — CALCULATE · FILTER · SUMX
// 특정 조건만 적용
Seoul Sales := CALCULATE([Total Sales], Customers[Region]="Seoul")
// 필터 제거 후 전체 대비 비율
Category Share :=
DIVIDE([Total Sales],
CALCULATE([Total Sales], ALL(Products[Category])))
// 행마다 금액 계산 후 합계
Revenue (Qty*Price) := SUMX(Sales, Sales[Qty] * RELATED(Products[Price]))TIP DIVIDE로 0 나눗셈 보호. ALL/ALLEXCEPT/KEEPFILTERS로 필터 조작을 미세 조정.
6. 날짜 테이블 & YTD/MTD
// Date 테이블에 연/월/분기 컬럼 추가 후, 관계 연결
Sales YTD := TOTALYTD([Total Sales], 'Date'[Date])
Sales MTD := TOTALMTD([Total Sales], 'Date'[Date])날짜 테이블은 연속 날짜여야 하며, 모델에서 날짜 테이블로 표시를 설정.
7. 실무 측정값 레시피 12가지
Avg Order Value := DIVIDE([Total Sales],[Orders])
Last Month Sales := CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH))
% to Last Month := DIVIDE([Total Sales]-[Last Month Sales],[Last Month Sales])
Top 10 Product Sales := CALCULATE([Total Sales], TOPN(10, VALUES(Products[Name]), [Total Sales], DESC))
New Customers :=
VAR prev = CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), DATESINPERIOD('Date'[Date], MIN('Date'[Date]), -1, YEAR))
RETURN DISTINCTCOUNT(Customers[CustomerID]) - prev
Returning Customers :=
CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), FILTER(Customers, [New Customers]=0))8. 챌린지 퀴즈(정답 숨김)
- DAX에서 행 컨텍스트와 필터 컨텍스트의 차이를 한 줄로 설명해 보세요.
CALCULATE([Total Sales], ALL('Date'))는 어떤 상황에 쓰나요?SUMX를SUM대신 쓰는 대표 사례는?- YTD 계산이 0으로만 나온다면 가장 먼저 확인할 것은?
- 카테고리 점유율 측정값에서 분모가 올바르지 않다면 어떤 함수로 필터를 제거/보정하나요?
정답 보기
- 행 컨텍스트=현재 행 기준 계산, 필터 컨텍스트=슬라이서/행·열 머리글 등 보고서 필터 상태.
- 기간 필터 무시 후 전체 누적 대비 비율 계산 등.
- 행마다 수식(수량×단가 등) 평가 후 합산해야 할 때.
- 연속 날짜의 날짜 테이블 존재 여부와 관계 설정.
ALL/ALLEXCEPT/KEEPFILTERS로 컨텍스트를 조정.