피벗 테이블 데이터 모델에서 계산 필드(Calculated Field)를 만들고 싶으신가요? 이 글에서는 데이터 모델에 계산 필드를 추가하는 다양한 방법을 예제와 함께 단계별로 자세히 소개합니다.
계산 필드란 무엇인가?
계산 필드는 피벗 테이블(PivotTable) 또는 피벗 차트(Pivot Chart)의 핵심 기능 중 하나로, 측정값(Measure)이라고도 부릅니다. 계산 필드, 즉 측정값은 DAX 수식을 사용해 새로 생성하는 필드를 의미합니다. 피벗 테이블 작성 후 데이터셋에 대해 추가적인 값을 계산하고 싶다면, 계산 필드를 만들어 손쉽게 원하는 수식을 적용할 수 있습니다.
계산 필드는 크게 두 가지 유형으로 나뉩니다.
- 암시적 계산 필드(Implicit Calculated Field): 피벗 테이블 필드 창을 통해 생성
- 명시적 계산 필드(Explicit Calculated Field): 피벗 테이블 분석 탭을 통해 생성
피벗 테이블 데이터 모델에서 계산 필드 만들기: 4가지 예제
아래 데이터셋에는 회사 직원들의 급여 목록과 근무일수가 정리되어 있습니다. 이번 예제에서는 직원들에게 지급할 보너스를 구하는 계산 필드를 만들어 보겠습니다.
본 글은 Microsoft Excel 365 버전을 기준으로 작성되었으며, 사용 중인 버전에 맞게 동일하게 따라 하실 수 있습니다.
예제 1: 피벗 테이블 필드로 암시적 계산 필드 만들기
이번 예제에서는 데이터셋을 피벗 테이블로 변환한 뒤, 급여의 30%를 보너스로 계산하는 계산 필드를 삽입합니다.
1단계: 피벗 테이블 만들기
먼저 계산 필드를 삽입할 피벗 테이블을 생성합니다.
- 삽입(Insert) 탭 >> 피벗 테이블(PivotTable) 드롭다운 >> 테이블/범위에서(From Table/Range) 옵션 선택
그러면 피벗 테이블 from table or range 대화 상자가 나타납니다.
- 데이터셋을 Table/Range로 지정하고 새 워크시트(New Worksheet) 선택
- 이 데이터를 데이터 모델에 추가(Add this data to the Data Model) 옵션에 체크한 후 확인(OK) 클릭
이후 새 시트로 이동하면 PivotTable1 영역과 피벗 테이블 필드 창이 표시됩니다.
- Employee 필드를 행(Rows) 영역으로, Salary 필드를 값(Values) 영역으로 끌어다 놓기
그러면 왼쪽에 아래와 같은 피벗 테이블이 생성됩니다.
2단계: 측정값 추가로 계산 필드 입력하기
이 단계에서는 DAX 수식을 활용해 측정값(계산 필드)을 추가합니다.
- 피벗 테이블 이름(여기서는 Range) 위에서 마우스 오른쪽 버튼 클릭
- 측정값 추가(Add Measure) 선택
그러면 측정값(Measure) 대화 상자가 열립니다.
- 측정값 이름(Measure Name)에 Bonus(원하는 이름) 입력
- 수식(Formula) 상자에서 등호(=) 뒤에 수식을 입력합니다. 여기서는 Salary 합계 필드가 필요하므로 's'를 입력하면 여러 옵션이 표시되는데, 그중 [Sum of Salary]를 선택합니다.
- 전체 수식을 완성합니다.
=[Sum of Salary]*0.03보너스가 개인 급여의 30%이므로 [Sum of Salary]에 0.03을 곱했습니다.
- 확인(OK) 클릭
이렇게 하면 계산 필드 Bonus가 필드 목록에 나타납니다.
- Bonus 필드를 값(Values) 영역으로 드래그
그러면 계산된 값과 함께 새 열 Bonus가 표시됩니다.
급여와 보너스에 통화 기호를 추가하면 다음과 같은 결과를 얻을 수 있습니다.
예제 2: Power Pivot 탭으로 암시적 계산 필드 만들기
이번 예제에서는 Power Pivot 탭을 활용해 DAX 수식으로 계산 필드 Bonus를 생성합니다.
1단계: Power Pivot 옵션 활성화하기
워크시트에 Power Pivot 탭이 보이지 않는다면, 아래 절차에 따라 옵션을 활성화하세요.
- 파일(File) 메뉴로 이동
- 옵션(Options) 선택
그러면 Excel 옵션 마법사가 열립니다.
- 추가 기능(Add-ins) 탭으로 이동한 뒤, 관리(Manage) 옆의 드롭다운 화살표 클릭
- COM 추가 기능(COM Add-ins)을 선택하고 이동(Go) 클릭
이어서 COM 추가 기능 마법사가 열립니다.
- Microsoft Power Pivot for Excel 옵션에 체크하고 확인(OK) 클릭
그러면 통합 문서에 Power Pivot 탭이 나타납니다.
2단계: 측정값 추가로 계산 필드 입력하기
- 예제 1의 1단계를 반복해 아래와 같은 시트를 엽니다.
- Employee 필드를 행(Rows) 영역으로, Salary 필드를 값(Values) 영역으로 드래그
그러면 왼쪽에 피벗 테이블이 생성됩니다.
- 피벗 테이블 내 임의의 셀을 선택한 뒤, Power Pivot 탭 >> 측정값(Measures) 드롭다운 >> 새 측정값(New Measure) 클릭
그러면 측정값(Measure) 대화 상자가 나타납니다.
- 측정값 이름을 Bonus 1로 설정
- 수식(Formula) 상자에 아래 수식 입력
=[Sum of Salary]*0.03
이 대화 상자에서 서식도 함께 지정할 수 있습니다.
- 범주(Category)를 통화(Currency)로 설정하고 소수 자릿수를 2로 지정
- 마지막으로 확인(OK) 클릭
그러면 직원들의 보너스가 담긴 Bonus 1 열이 피벗 테이블에 자동으로 추가됩니다.
급여에 통화 기호를 붙이고 표에 테두리를 적용하면 최종 결과는 아래 그림과 같습니다.
예제 3: 피벗 테이블 분석 탭으로 명시적 계산 필드 만들기
이번에는 계산 필드(Calculated Field) 기능을 직접 사용해, 직원 보너스 금액을 구하는 명시적 계산 필드를 수식과 함께 생성합니다.
단계:
- 예제 1의 1단계를 반복해 아래와 같은 시트를 엽니다.
- Employee 필드를 행(Rows) 영역으로, Salary 필드를 값(Values) 영역으로 드래그
그러면 왼쪽에 피벗 테이블이 생성됩니다.
- 피벗 테이블 내 임의의 셀을 선택한 뒤, PivotTable 분석(Analyze) 탭 >> 계산(Calculations) 그룹 >> 필드, 항목 및 집합(Fields, Items & Sets) 드롭다운 >> 계산 필드(Calculated Field) 클릭
그러면 계산 필드 삽입(Insert Calculated Field) 대화 상자가 열립니다.
- 이름(Name)을 Bonus 2로 설정
- 수식(Formula) 상자의 등호(=) 뒤에 원하는 필드를 삽입합니다.
- Salary 필드를 클릭하고 필드 삽입(Insert Field) 클릭
그러면 Salary가 수식 상자에 표시됩니다.
- 아래와 같이 수식을 완성합니다.
= Salary*0.03- 확인(OK) 클릭
마지막으로 보너스 값이 담긴 새 열이 피벗 테이블에 추가됩니다.
급여에 통화 기호를 붙이고 표에 테두리를 적용하면 최종 결과는 아래 그림과 같습니다.
예제 4: 복잡한 수식에 명시적 계산 필드 활용하기
이번에는 조건이 있는 보너스를 계산해 보겠습니다. 근무일수가 250일을 초과하는 직원에게는 급여의 50%를, 그 외 직원에게는 급여의 30%를 보너스로 지급합니다. 이 조건을 적용하기 위해 IF 함수를 사용합니다.
단계:
- 예제 1의 1단계를 반복해 아래와 같은 시트를 엽니다.
- Employee 필드를 행(Rows) 영역으로, Working Days와 Salary 필드를 값(Values) 영역으로 드래그
그러면 왼쪽에 피벗 테이블이 생성됩니다.
- 피벗 테이블 내 임의의 셀을 선택한 뒤, PivotTable 분석(Analyze) 탭 >> 계산(Calculations) 그룹 >> 필드, 항목 및 집합 드롭다운 >> 계산 필드(Calculated Field) 클릭
그러면 계산 필드 삽입 대화 상자가 열립니다.
- 이름(Name)을 Bonus 3으로 설정
- 수식(Formula) 상자의 등호(=) 뒤에 원하는 필드를 삽입합니다.
- 먼저 IF(를 입력해 수식을 시작합니다.
- Working Days 필드를 클릭하고 필드 삽입(Insert Field) 클릭
이런 방식으로 필요한 필드들을 수식에 하나씩 삽입합니다.
- 아래 수식을 적용합니다.
=IF('Working Days' >250,Salary *0.05,Salary *0.03)- 확인(OK) 클릭
마지막으로 계산된 보너스가 담긴 새 열이 표시됩니다.
급여와 보너스에 통화 기호를 붙이고 표에 테두리를 적용하면 최종 결과는 아래 그림과 같습니다.
피벗 테이블 데이터 모델에서 계산 필드 삭제하는 방법
이번에는 아래 피벗 테이블에서 계산 필드인 Sum of Bonus 3을 제거하는 방법을 알아보겠습니다.
단계:
- 계산 필드로 생성된 열의 임의의 셀을 선택한 뒤 마우스 오른쪽 버튼 클릭
- Remove "Sum of Bonus 3"(Sum of Bonus 3 제거) 선택
이렇게 하면 계산 필드가 삭제됩니다.
피벗 테이블 데이터 모델의 계산 필드 수식 목록 확인 방법
계산 필드에 사용된 수식 목록을 확인하고 싶다면 아래 절차를 따르세요.
단계:
- 피벗 테이블 내 임의의 셀을 선택한 뒤, PivotTable 분석(Analyze) 탭 >> 계산(Calculations) 그룹 >> 필드, 항목 및 집합 드롭다운 >> 수식 나열(List Formulas) 클릭
그러면 새 시트로 이동하면서 상세한 수식 목록을 확인할 수 있습니다.
결론
이 글에서는 피벗 테이블 데이터 모델에서 계산 필드를 만드는 방법을 네 가지 예제와 함께 살펴보았습니다. 도움이 되었기를 바랍니다. 제안이나 궁금한 점이 있다면 댓글로 자유롭게 남겨주세요.
함께 읽으면 좋은 글
- 엑셀 데이터 모델 활용법 (3가지 예제)
- 엑셀 데이터 모델 관리하기 (쉬운 단계별 가이드)