계산 열과 계산 필드를 사용하는 경우

파워 피벗을 사용하는 방법을 처음 학습할 때 대부분의 사용자는 실제 전력이 어떤 방식으로든 결과를 집계하거나 계산하고 있다는 것을 알게 됩니다. 데이터에 숫자 값이 있는 열이 있는 경우 피벗 테이블 또는 파워 뷰 필드 목록에서 선택하여 쉽게 집계할 수 있습니다. 기본적으로 숫자이므로 자동으로 합계, 평균, 개수 또는 선택한 집계 유형이 합계가 계산됩니다. 이를 암시적 측정값이라고 합니다. 암시적 측정값은 빠르고 쉬운 집계에 적합하지만 제한이 있으며 명시적 측정값 및 계산 열로 이러한 제한을 거의 항상 극복할 수 있습니다.

먼저 계산 열을 사용하여 Product라는 테이블의 각 행에 대한 새 텍스트 값을 추가하는 예제를 살펴보겠습니다. Product 테이블의 각 행에는 판매 중인 각 제품에 대한 모든 종류의 정보가 포함됩니다. 제품 이름, 색, 크기, 딜러 가격 등에 대한 열이 있습니다. ProductCategoryName 열을 포함하는 Product Category라는 또 다른 관련 테이블이 있습니다. 제품 테이블의 각 제품에 제품 범주 테이블의 제품 범주 이름을 포함할 수 있습니다. Product 테이블에서 다음과 같이 Product Category라는 계산 열을 만들 수 있습니다.

새 제품 범주 수식은 RELATED DAX 함수를 사용하여 관련 Product Category 테이블의 ProductCategoryName 열에서 값을 가져옵니다. 그런 다음 Product 테이블에 각 제품(각 행)에 대한 값을 입력합니다.

이는 계산 열을 사용하여 나중에 피벗 테이블의 ROWS, COLUMNS 또는 FILTERS 영역 또는 파워 뷰 보고서에서 사용할 수 있는 각 행에 대해 고정 값을 추가하는 방법에 대한 좋은 예입니다.

제품 범주에 대한 이익률을 계산하려는 또 다른 예제를 만들어 보겠습니다. 이는 많은 자습서에서도 일반적인 시나리오입니다. 데이터 모델에는 트랜잭션 데이터가 있는 Sales 테이블이 있으며 Sales 테이블과 Product Category 테이블 간에 관계가 있습니다. Sales 테이블에는 판매 금액이 있는 열과 비용이 있는 다른 열이 있습니다.

다음과 같이 SalesAmount 열의 값에서 COGS 열의 값을 빼서 각 행의 이익 금액을 계산하는 계산 열을 만들 수 있습니다.

이제 피벗 테이블을 만들고 제품 범주 필드를 COLUMNS로 끌어와 새 이익 필드를 값 영역으로 끌어다 놓을 수 있습니다(PowerPivot 테이블의 열은 피벗 테이블 필드 목록의 필드임). 결과는 수익 합계라는 암시적 측정값입니다. 각 제품 범주에 대한 수익 열의 집계된 값입니다. 결과는 다음과 같습니다.

이 경우 Profit은 VALUES의 필드로만 의미가 있습니다. COLUMNS 영역에 Profit을 배치하는 경우 피벗 테이블은 다음과 같습니다.

수익 필드는 COLUMNS, ROWS 또는 FILTERS 영역에 배치되는 경우 유용한 정보를 제공하지 않습니다. 값 영역에서 집계된 값으로만 의미가 있습니다.

수행한 작업은 Sales 테이블의 각 행에 대한 이익률을 계산하는 Profit이라는 열을 만드는 것입니다. 그런 다음 피벗 테이블의 VALUES 영역에 Profit을 추가하여 각 제품 범주에 대해 결과가 계산되는 암시적 측정값을 자동으로 만듭니다. 제품 범주에 대한 수익을 두 번 계산한 것이 맞습니다. 먼저 Sales 테이블의 각 행에 대한 수익을 계산한 다음 각 제품 범주에 대해 집계된 VALUES 영역에 Profit을 추가했습니다. 또한 Profit 계산 열을 만들 필요가 없다고 생각하는 경우에도 맞습니다. 그러나 Profit 계산 열을 만들지 않고 수익을 계산하려면 어떻게 해야 할까요?

이익, 정말 더 나은 명시적 측정으로 계산 될 것입니다.

지금은 Sales 테이블 및 제품 범주의 Profit 계산 열을 피벗 테이블의 COLUMNS 및 Profit in VALUES에 남겨 두어 결과를 비교하겠습니다.

Sales 테이블의 계산 영역에서 명명 충돌을 방지하기 위해 Total Profit이라는 측정값을 만듭니다. 결국 Profit 계산 열이 없으면 이전과 동일한 결과를 얻을 수 있습니다.

먼저 Sales 테이블에서 SalesAmount 열을 선택한 다음 자동 합계를 클릭하여 SalesAmount의 명시적 합계 측정값을 만듭니다. 명시적 측정값은 Power Pivot에 있는 테이블의 계산 영역에서 만드는 측정값입니다. COGS 열도 마찬가지입니다. 더 쉽게 식별할 수 있도록 이러한 Total SalesAmount 및 Total COGS 의 이름을 바꿉니다.

그런 다음 이 수식을 사용하여 다른 측정값을 만듭니다.

총 이익:=[ Total SalesAmount] - [Total COGS]

새 Total Profit 측정값의 형식을 통화로 변경한 후 피벗 테이블에 추가할 수 있습니다.

새 Total Profit 측정값은 Profit 계산 열을 만든 다음 VALUES에 배치하는 것과 동일한 결과를 반환하는 것을 볼 수 있습니다. 차이점은 Total Profit 측정값이 훨씬 더 효율적이며, 피벗 테이블에 대해 선택한 필드에 대해서만 계산하고 있기 때문에 데이터 모델을 더 깨끗하고 날씬하게 만듭니다. 결국 Profit 계산 열은 필요하지 않습니다.

이 마지막 부분이 중요한 이유는 무엇인가요? 계산 열은 데이터 모델에 데이터를 추가하고 데이터는 메모리를 차지합니다. 데이터 모델을 새로 고치면 Profit 열의 모든 값을 다시 계산하기 위해 처리 리소스도 필요합니다. 제품 범주, 지역 또는 날짜와 같이 피벗 테이블에서 수익이 필요한 필드를 선택할 때 수익을 계산하려고 하기 때문에 이와 같은 리소스를 실제로 사용할 필요가 없습니다.

또 다른 예제를 살펴보겠습니다. 계산 열이 언뜻 보기에는 정확해 보이지만...

이 예제에서는 총 매출의 백분율로 판매액을 계산하려고 합니다. 다음과 같이 Sales 테이블에 Sales %라는 계산 열을 만듭니다.

수식은 Sales 테이블의 각 행에 대해 SalesAmount 열의 금액을 SalesAmount 열에 있는 모든 금액의 합계 합계로 나눕니다.

피벗 테이블을 만들고 COLUMNS에 제품 범주를 추가하고 새 Sales % 열을 선택하여 값에 넣으면 각 제품 범주에 대한 총 판매액의 합계가 표시됩니다.

그래. 이것은 지금까지 좋아 보인다. 하지만 슬라이서 추가해 보겠습니다. 달력 연도를 추가한 다음, 연도를 선택합니다. 이 경우 2007을 선택합니다. 이것이 우리가 얻는 것입니다.

언뜻 보기에는 여전히 올바른 것처럼 보일 수 있습니다. 그러나 2007년 각 제품 범주의 총 매출 비율을 알고 싶기 때문에 백분율은 실제로 총 100%여야 합니다. 그래서 무엇이 잘못되었습니까?

Sales 열의 %는 SalesAmount 열의 값인 각 행에 대해 SalesAmount 열에 있는 모든 값의 합계 합계로 나눈 백분율을 계산했습니다. 계산 열의 값은 고정되어 있습니다. 테이블의 각 행에 대해 변경할 수 없는 결과입니다. 피벗 테이블에 Sales %를 추가했을 때 SalesAmount 열에 있는 모든 값의 합계로 집계되었습니다. Sales 열의 %에 있는 모든 값의 합계는 항상 100%입니다.

도움이 되지 않으므로 Sales 계산 열의 %를 삭제할 수 있습니다. 대신 적용된 필터 또는 슬라이서에 관계없이 총 매출의 백분율을 올바르게 계산하는 측정값을 만들겠습니다.

이전에 만든 TotalSalesAmount 측정값을 기억하세요. SalesAmount 열을 간단히 합산하는 측정값은 무엇입니까? Total Profit 측정값의 인수로 사용했으며 새 계산 필드에서 인수로 다시 사용하겠습니다.

다음 수식을 사용하여 새 측정값을 만듭니다.

%of Total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

이 수식은 Total SalesAmount의 결과를 피벗 테이블에 정의된 열 또는 행 필터가 없는 SalesAmount의 합계 합계로 나눕니다.

이제 피벗 테이블에 총 매출의 새 %를 추가하면 다음을 얻을 수 있습니다.

그것은 더 나은 보인다. 이제 각 제품 범주 의 총 매출 비율 은 2007년 총 매출의 백분율로 계산됩니다. CalendarYear 슬라이서에서 다른 연도 또는 1년 이상을 선택하면 제품 범주에 대한 새로운 백분율이 표시되지만 총합계는 여전히 100%입니다. 다른 슬라이서와 필터도 추가할 수 있습니다. Total Sales 측정값의 %는 적용된 슬라이서 또는 필터에 관계없이 항상 총 매출의 백분율을 생성합니다. 측정값을 사용하면 결과는 COLUMNS 및 ROWS의 필드와 적용된 필터 또는 슬라이서에 의해 결정되는 컨텍스트에 따라 항상 계산됩니다. 이것이 측정값의 힘입니다.

다음은 계산 열 또는 측정값이 특정 계산 요구 사항에 적합한지 여부를 결정할 때 도움이 되는 몇 가지 지침입니다.

계산 열 사용

• 새 데이터를 행, 열 또는 피벗 테이블의 FILTERS 또는 POWER View 시각화의 AXIS, LEGEND 또는 TILE BY에 표시하려면 계산 열을 사용해야 합니다. 데이터의 일반 열과 마찬가지로 계산된 열은 모든 영역에서 필드로 사용할 수 있으며 숫자인 경우 VALUES에서도 집계할 수 있습니다.

• 새 데이터를 행의 고정 값으로 지정하려는 경우 예를 들어 날짜 열이 있는 날짜 테이블이 있고 월 수만 포함하는 다른 열을 원합니다. 날짜 열의 날짜에서 월 번호만 계산하는 계산 열을 만들 수 있습니다. 예를 들어 =MONTH('Date'[Date])입니다.

• 테이블에 각 행에 대한 텍스트 값을 추가하려면 계산 열을 사용합니다. 텍스트 값이 있는 필드는 VALUES에서 집계할 수 없습니다. 예를 들어 =FORMAT('Date'[Date],"mmmm")은 Date 테이블의 Date 열에 있는 각 날짜의 월 이름을 제공합니다.

측정값 사용

• 계산 결과가 항상 피벗 테이블에서 선택한 다른 필드에 종속되는 경우.

• 특정 종류의 필터를 기반으로 개수를 계산하거나 전년 대비 또는 분산을 계산하는 등 더 복잡한 계산을 수행해야 하는 경우 계산 필드를 사용합니다.

• 통합 문서의 크기를 최소한으로 유지하고 성능을 최대화하려면 가능한 한 많은 계산을 만듭니다. 대부분의 경우 모든 계산은 측정값이 될 수 있으므로 통합 문서 크기를 크게 줄이고 새로 고침 시간을 단축할 수 있습니다.

Profit 열과 같이 계산 열을 만든 다음 피벗 테이블 또는 보고서에서 집계하는 데는 아무런 문제가 없습니다. 그것은 실제로 배우고 자신의 계산을 만들 수있는 정말 좋은 쉬운 방법입니다. Power Pivot의 이 두 가지 매우 강력한 기능에 대한 이해가 커짐에 따라 가장 효율적이고 정확한 데이터 모델을 만들 수 있습니다. 여기서 배운 내용이 도움이 되기를 바랍니다. 도움이 될 수 있는 다른 정말 좋은 리소스가 있습니다. 다음은 DAX 수식의 컨텍스트, 파워 피벗의 집계 및 DAX 리소스 센터입니다. 또한 좀 더 고급적이고 회계 및 재무 전문가를 대상으로 하는 반면 Excel의 Microsoft Power Pivot을 사용하는 이익 및 손실 데이터 모델링 및 분석 샘플에는 훌륭한 데이터 모델링 및 수식 예제가 로드됩니다.