엑셀 데이터 모델링과 DAX 측정값 만들기

표 두 개를 관계로 잇는 건 시작일 뿐이에요. 실제 모델은 여러 표를 연결해요. 거래 내역이 가득한 팩트 테이블 하나를, 거래마다 언제, 무엇을, 어디서 팔았는지 설명하는 작은 차원 테이블들이 둘러싸는 구조죠. 이 구조만 제대로 잡으면 DAX 측정값 몇 개로 "지역별 매출", "East만", "작년 대비"를 어떤 피벗 테이블(PivotTable)에서든 구할 수 있어요. 도우미 열은 하나도 필요 없어요.

이 글은 데이터 모델(Data Model)에서 표, 관계, 간단한 측정값(measure)을 만들 줄 안다는 전제로 설명해요.

스타 스키마 설계하기

예시 모델은 팩트 테이블 하나와 차원 테이블 세 개로 이루어져 있어요.

• Sales(팩트): 판매 한 건당 한 행이에요. Date, ProductID, RegionID, Revenue, Cost 열이 있어요.
• Products(차원): ProductID 하나당 한 행이에요. Name, Category 열이 있어요.
• Regions(차원): RegionID 하나당 한 행이에요. Region, Manager 열이 있어요.
• Calendar(차원): 하루당 한 행이에요. Date, Year, Month 열이 있고, 뒤에서 직접 만들어요.

fx

	A	B	C	D	E
1	Date	ProductID	RegionID	Revenue	Cost
2	2024-01-05	P1	R1	1,200	800
3	2024-01-18	P2	R2	900	550
4	2024-02-03	P1	R2	1,400	900
5	2024-02-20	P2	R1	1,100	700
6	2025-01-10	P1	R1	1,600	1,000

fx

	A	B	C	D	E	F	G
1	ProductID	Name	Category		RegionID	Region	Manager
2	P1	Widget Core	Hardware		R1	East	Ana
3	P2	Widget Plus	Hardware		R2	West	Bo

모든 차원은 팩트 테이블에 직접 연결되고, 그 밖의 어떤 표와도 연결되지 않아요. 그려 보면 팩트 테이블이 가운데에 있고 차원이 그 주위를 둘러싸요. 그래서 스타 스키마(star schema)라고 불러요.

             Products
                |
Calendar --- Sales --- Regions

별 모양을 깔끔하게 유지하는 규칙은 두 가지예요.

• 차원끼리 사슬처럼 잇지 않아요. Regions를 Products에, Products를 Calendar에 연결하지 마세요. 지역의 국가처럼 차원에 정보가 더 필요하면, 그 차원에 매달린 새 표를 만들지 말고 그 차원 안에 열로 추가하세요.
• 팩트끼리 연결하지 않아요. RegionID별 월간 목표(Targets) 같은 두 번째 팩트 테이블을 추가한다면, 같은 Regions 차원에 연결하세요. 그러면 Region을 행 영역에 넣은 피벗 테이블에서 매출과 목표를 나란히 볼 수 있어요. 차원 하나가 두 팩트 테이블을 함께 필터링하기 때문이에요.

관계는 데이터› then 데이터 도구› then 관계로 만들거나, 파워 피벗(Power Pivot) 창의 다이어그램 보기에서 키끼리 끌어다 놓아 만들어요. Sales[ProductID]는 Products[ProductID]에, Sales[RegionID]는 Regions[RegionID]에 연결하세요.

필터는 관계를 따라 어떻게 흐를까요?

Regions[Region]을 피벗 테이블의 행 영역에 넣으면, 행마다 Regions 표를 지역 하나로 필터링해요. 이 필터는 관계를 따라 "일" 쪽(Regions)에서 "다" 쪽(Sales)으로 흘러가요. 그래서 East 행은 East 판매만 보게 돼요.

엑셀에서 만든 관계는 이 한 방향으로만 필터링해요. Sales에 건 필터는 Regions로 거슬러 올라가지 않고, Products에 건 필터는 Regions에 전혀 닿지 않아요. 차원은 각자 Sales만 필터링하죠. 바로 이게 원하는 동작이고, 스타 모양 덕분에 예측하기도 쉬워요. 모든 차원은 팩트 테이블을 필터링하고, 모든 숫자는 팩트 테이블에 있어요.

다대다(many-to-many) 연결은 이 예측 가능성을 깨뜨려요. Sales와 Targets를 RegionID로 직접 연결하려고 하면 두 열 모두 값이 중복되어서, 필터가 출발할 "일" 쪽이 없어요. 엑셀은 이 관계를 만들지 않고, 적어도 한 열은 값이 고유해야 한다고 알려 줘요. 대신 두 팩트 테이블이 공유하는 차원을 거치게 하세요.

필터 컨텍스트: 측정값 하나, 답은 여러 개

같은 측정값이 피벗 테이블의 셀마다 다른 숫자를 돌려주는 건, 셀마다 자기만의 필터 컨텍스트(filter context)가 있기 때문이에요. 행 레이블, 열 레이블, 슬라이서(Slicer), 보고서 필터가 합쳐진 필터죠. 측정값 자체는 전혀 바뀌지 않아요.

Regions[Region]을 행 영역에 넣으면 SUM(Sales[Revenue])는 East에서 3,900(1,200 + 1,100 + 1,600), West에서 2,300을 돌려줘요. Calendar[Year]를 열 영역에 추가하면 East/2024 셀은 2,300으로 더 좁혀져요. 슬라이서를 클릭하면 모든 셀이 다시 좁혀지고요.

그래서 보고서에서는 측정값이 계산된 열(calculated column)보다 나아요. 계산된 열은 행마다 한 번 계산해서 저장해 두지만, 측정값은 놓인 자리의 필터 컨텍스트에 맞춰 매번 다시 계산돼요.

측정값 테이블 만들기

측정값은 어떤 표에 속해야 하고, 보통은 Sales에 넣게 돼요. 측정값이 30개쯤 되면 열 사이에 파묻혀 버리죠. 측정값만 담는 전용 측정값 테이블을 두면, 모델을 여는 누구든 모든 계산을 한곳에서 찾을 수 있어요.

1. 자리 표시용 표를 만드세요

   빈 시트의 A1에 Placeholder를 입력하고, A1을 선택한 상태에서 Ctrl+T를 누르세요. 머리글 포함을 체크하고 확인을 클릭하세요. 테이블 디자인 탭에서 표 이름을 _Measures로 정하세요. 밑줄을 붙이면 데이터 표와 확실히 구분돼요.

2. 데이터 모델에 추가하세요

   표 안의 셀을 선택한 상태에서 Power Pivot› then 테이블› then 데이터 모델에 추가를 클릭하세요. 파워 피벗 창이 열리고 새 표가 보여요.

3. 자리 표시용 열을 숨기세요

   파워 피벗 창에서 Placeholder 열 머리글을 마우스 오른쪽 단추로 클릭하고 클라이언트 도구에서 숨기기를 선택하세요. 이 열이 피벗 테이블 필드 목록에서 사라지고, _Measures 아래에는 측정값만 남게 돼요.

4. 새 측정값은 모두 여기에 만드세요

   측정값을 만들 때 측정값 대화 상자에서 테이블 이름을 _Measures로 정하세요. 이미 있는 측정값을 옮기려면 Power Pivot› then 계산› then 측정값› then 측정값 관리를 열고 측정값을 선택한 다음 편집을 클릭해 테이블 이름을 바꾸세요.

핵심 측정값 작성하기

Power Pivot› then 계산› then 측정값› then 새 측정값으로 _Measures에 아래 측정값을 만드세요. 측정값 이름에 이름을, 수식에 수식을 넣어요.

뒤의 측정값은 앞의 측정값을 대괄호 안에 이름으로 넣어 참조해요. 그래서 매출의 정의는 한곳에만 있어요. 반품을 빼야 하는 식으로 정의가 바뀌면 Total Revenue만 고치면 되고, 그 위에 쌓은 측정값은 모두 따라 바뀌어요.

비율은 항상 DIVIDE로 계산하세요. DAX는 워크시트 수식처럼 #DIV/0!을 돌려주지 않아요. /로 0을 나누면 Infinity나 NaN이 나오고, 이 단어가 보고서에 그대로 찍혀요. 판매가 전부 환불되어 매출이 0이 된 제품 같은 경우죠. DIVIDE는 이럴 때 빈 값을 돌려주고, 데이터 모델 기반 피벗 테이블은 값이 모두 빈 행을 숨겨요.

CALCULATE로 필터 컨텍스트 바꾸기

CALCULATE는 측정값을 바뀐 필터 컨텍스트에서 계산해요. 첫 번째 인수는 측정값이고, 그 뒤의 인수는 측정값을 계산하기 전에 적용할 필터예요.

East Revenue := CALCULATE([Total Revenue], Regions[Region] = "East")

Revenue All Regions := CALCULATE([Total Revenue], ALL(Regions))

Region Share := DIVIDE([Total Revenue], [Revenue All Regions])

위 형태(이름 := 수식)는 파워 피벗 창 데이터 보기의 계산 영역에 그대로 입력할 수 있어요. 측정값 대화 상자에서는 측정값 이름에 이름을, 수식에 =로 시작하는 수식을 입력하세요. 예를 들면 =CALCULATE([Total Revenue], Regions[Region] = "East")예요.

• East Revenue는 Regions[Region]에 걸린 필터를 "East"로 바꿔치기해요. West 행에서도 East의 3,900을 돌려주는데, CALCULATE의 필터가 그 열에 걸린 행 필터를 덮어쓰기 때문이에요. Year 슬라이서처럼 다른 열에 걸린 필터는 그대로 적용돼요.
• Revenue All Regions는 ALL로 Regions 표의 필터를 모두 없애요. 그래서 모든 행이 전체 지역 합계(6,200)를 보게 돼요.
• Region Share는 앞의 둘을 나눠요. East는 3,900 / 6,200 = 62.9%예요.

fx

	A	B	C	D	E
1	행 레이블	Total Revenue	Margin %	East Revenue	Region Share
2	East	3,900	35.9%	3,900	62.9%
3	West	2,300	37%	3,900	37.1%
4	총합계	6,200	36.3%	3,900	100%

작년과 비교하기

SAMEPERIODLASTYEAR 같은 시간 인텔리전스(time intelligence) 함수는 현재 필터 컨텍스트의 날짜를 1년 전으로 옮겨요. 그러려면 제대로 된 날짜 테이블이 필요해요. 날짜마다 한 행씩, 빠진 날 없이, 연도 전체를 담고 있어야 해요.

1. 모든 날짜를 나열하세요

   새 시트의 A1에 Date, A2에 2024-01-01을 입력하세요. A2를 선택하고 홈› then 편집› then 채우기› then 계열로 가세요. 방향은 열, 유형은 날짜, 날짜 단위는 일, 단계 값은 1, 종료 값은 2025-12-31로 정하고 확인을 클릭하세요. A2:A732에 연속된 날짜 731개가 채워져요.

2. Year, Month 열이 있는 표로 만드세요

   Ctrl+T를 누르고 표 이름을 Calendar로 정하세요. B1에 Year, C1에 Month를 입력한 다음 B2와 C2에 아래 수식을 입력하세요. 표 안의 계산된 열이라서 수식이 열 전체에 채워져요.

   =YEAR([@Date])복사

   =TEXT([@Date],"yyyy-mm")복사

3. 모델에 추가하고 날짜 테이블로 표시하세요

   Power Pivot› then 테이블› then 데이터 모델에 추가를 클릭하세요. 파워 피벗 창의 디자인 탭에서 날짜 테이블로 표시를 클릭하고 Date 열을 고르세요.

4. Sales와 연결하세요

   Sales[Date]와 Calendar[Date]를 관계로 연결하세요. 이제부터 피벗 테이블에는 Sales[Date]가 아니라 Calendar[Year]와 Calendar[Month]를 쓰세요.

5. 비교 측정값을 작성하세요

   _Measures에 아래 측정값 두 개를 만드세요. 표 이름을 작은따옴표로 감싼 건 CALENDAR가 DAX 함수 이름이기도 하기 때문이에요. 표 이름은 언제든 따옴표로 감쌀 수 있으니 헷갈리지 않게 감싸 두세요.

   Revenue LY := CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Calendar'[Date]))
   

   Revenue YoY % := DIVIDE([Total Revenue] - [Revenue LY], [Revenue LY])
   

Calendar[Year]를 행 영역에, 매출 측정값 세 개를 값 영역에 넣어 보세요.

fx

	A	B	C	D
1	행 레이블	Total Revenue	Revenue LY	Revenue YoY %
2	2024	4,600
3	2025	1,600	4,600	-65.2%

이 보고서는 꼼꼼히 읽어야 해요. 2025년은 65.2% 감소로 나오지만, 데이터에는 2025년 1월만 들어 있어요. 한 달 치 판매를 1년 치와 비교하고 있는 거죠. Calendar[Month]를 행 영역의 Year 아래에 추가하면 2025-01 행이 실제 모습을 보여 줘요. 2024년 1월의 2,100에 비해 1,600으로, 23.8% 감소예요. 시간 인텔리전스는 컨텍스트에 있는 날짜를 그대로 옮길 뿐, 아직 한 해가 끝나지 않았다는 건 알지 못해요.

자주 하는 실수

• Calendar[Date] 대신 Sales[Date] 쓰기. 시간 인텔리전스 함수에는 날짜 테이블의 빠짐없는 날짜 열이 필요해요. 팩트 테이블의 날짜로 묶으면 판매가 없는 날도 빠져요.
• 차원을 눈송이처럼 잇기. Regions → Countries → Continents처럼 이어도 동작은 하지만, 연결이 하나 늘 때마다 필터가 예상과 다르게 움직일 곳이 늘어나요. 추가 열은 Regions 안에 펼쳐 넣으세요.
• / 연산자로 비율 계산하기. 분모가 한 번만 0이어도 보고서에 Infinity나 NaN이 나타나요. DIVIDE를 쓰세요.
• 측정값을 여러 표에 흩어 두기. 반년 뒤에는 아무도 Margin %를 못 찾아요. 모두 _Measures에 모아 두세요.
• 필터가 "거꾸로" 흐를 거라고 기대하기. 행 영역에 Products[Name]만 있고 값 영역이 비어 있으면 Region 슬라이서를 눌러도 아무것도 바뀌지 않아요. Regions는 Sales를 필터링하지, Products를 필터링하지 않거든요. 값 영역에 측정값을 넣으면 그 지역에서 판매가 없는 제품이 빠져요.

연습

연습

작은 스타 스키마 만들기

1. 위 시트의 Sales 데이터와 Products, Regions 데이터를 새 통합 문서에 입력하고, 각각 Sales, Products, Regions라는 이름의 엑셀 표로 만드세요.
2. 세 표를 모두 데이터 모델에 추가하고, Sales[ProductID]를 Products[ProductID]에, Sales[RegionID]를 Regions[RegionID]에 연결하세요.
3. _Measures 표를 만들어 모델에 추가하고 자리 표시용 열을 숨기세요.
4. Total Revenue, Total Cost, Gross Margin, Margin %를 작성하세요. 행 영역에 Region을 넣은 피벗 테이블에서 East 3,900 / 35.9%, West 2,300 / 37%가 나오는지 확인하세요.
5. East Revenue, Revenue All Regions, Region Share를 추가하세요. East의 비중은 62.9%여야 해요.
6. Calendar 표를 만들어 Sales[Date]와 연결하고 Revenue LY와 Revenue YoY %를 작성하세요. 2025년 1월이 1,600과 2,100을 비교하는지 확인하세요.

그다음 업무에서 쓰는 평평한 내보내기 파일로도 해 보세요. 팩트 테이블 하나, 날짜 테이블 하나, 그리고 다른 차원 테이블을 하나 이상으로 나누고, 차원마다 잘라 볼 때 같은 측정값이 다른 답을 내는지 확인하세요.

실력 점검

• 팩트 테이블을 가운데 둔 내 데이터의 스타 스키마를 그릴 수 있나요?
• 두 팩트 테이블을 서로 연결하지 말고 차원을 공유하게 해야 하는 이유를 설명할 수 있나요?
• CALCULATE로 필터를 덮어쓰거나 없애는 측정값을 작성할 수 있나요?
• 측정값 안에서 DIVIDE가 /보다 안전한 이유를 설명할 수 있나요?
• 피벗 테이블의 어느 월이나 연도에서든 동작하는 전년 대비 비교를 만들 수 있나요?