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

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

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

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

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

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

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

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

이 경우 Profit은 VALUES의 필드로만 의미가 있습니다. COLUMNS 영역에 Profit을 넣으면 피벗 테이블은 다음과 같을 것입니다.

수익 필드는 열, 행 또는 필터 영역에 배치될 때 유용한 정보를 제공하지 않습니다. VALUES 영역에서 집계된 값으로만 의미가 있습니다.

지금까지 Sales 테이블의 각 행에 대한 이익률을 계산하는 Profit이라는 열을 만들었습니다. 그런 다음 피벗 테이블의 값 영역에 수익을 추가하여 각 제품 범주에 대해 결과가 계산되는 암시적 측정값을 자동으로 만듭니다. 우리가 실제로 제품 범주에 대한 수익을 두 번 계산했다고 생각한다면 당신의 생각이 맞습니다. 먼저 판매 테이블의 각 행에 대한 수익을 계산한 다음 각 제품 범주에 대해 집계된 값 영역에 수익을 추가했습니다. 수익 계산 열을 만들 필요가 없다고 생각한다면 이 말도 맞습니다. 그런데 이익 계산 열을 만들지 않고 어떻게 수익을 계산합니까?

이익은 명시적 척도로 계산하는 것이 더 나을 것입니다.

지금은 결과를 비교하기 위해 피벗 테이블의 Sales 테이블에 Profit 계산 열, COLUMNS에 Product Category를, VALUES에 수익을 그대로 둡니다.

판매 테이블의 계산 영역에서 이름 충돌을 방지하기 위해 총 수익 이라는 측정값을 만들 것입니다. 결국에는 이전에 했던 것과 동일한 결과가 생성되지만 수익 계산 열은 없습니다.

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

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

총 수익:=[총 매출액] - [총 COGS]

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

새 총 수익 측정값이 수익 계산 열을 만든 다음 VALUES에 배치하는 것과 동일한 결과를 반환하는 것을 볼 수 있습니다. 차이점은 피벗 테이블에 대해 선택한 필드에 대해서만 당시 계산하기 때문에 총 수익 측정값이 훨씬 효율적이고 데이터 모델이 더 명확하고 간결하다는 것입니다. 결국 수익 계산 열은 실제로 필요하지 않습니다.

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

다른 예를 살펴보겠습니다. 계산된 열이 언뜻보기에는 올바르게 보이는 결과를 만드는 곳이지만....

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

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

피벗 테이블을 만들고 제품 범주를 COLUMNS에 추가하고 새 매출 % 열을 선택하여 값에 넣으면 각 제품 범주에 대한 매출 합계 %를 얻게 됩니다.

확인. 지금까지는 괜찮아 보입니다. 하지만 슬라이서를 추가합시다. Calendar Year를 추가한 다음 연도를 선택합니다. 이 경우 2007을 선택합니다. 이것이 우리가 얻는 것입니다.

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

매출 비율 열에서는 각 행에 대한 백분율을 계산합니다. 즉, 매출액 열의 값을 매출액 열의 모든 값의 합계로 나눈 값입니다. 계산된 열의 값은 고정됩니다. 테이블의 각 행에 대해 변경할 수 없는 결과입니다. 피벗 테이블에 매출 % 를 추가하면 매출 금액 열에 있는 모든 값의 합계로 집계되었습니다. 매출 비율 열에 있는 모든 값의 합계는 항상 100%입니다.

판매액 % 계산 열은 도움이 되지 않으므로 삭제할 수 있습니다. 대신, 필터나 슬라이서가 적용되었는지에 관계없이 총 매출 비율을 올바르게 계산하는 측정값을 만들겠습니다.

앞서 만든 TotalSalesAmount 측정값, 단순히 SalesAmount 열의 합계를 구하는 측정값을 기억하시나요? 총 수익 측정값에서 인수로 사용했으며 새 계산 필드에서 다시 인수로 사용할 것입니다.

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

총 매출 비율:=([총 판매액]) / CALCULATE([총 판매액], ALLSELECTED())

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

이제 새 총 매출 비율 을 피벗 테이블에 추가하면 다음을 얻습니다.

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

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

계산된 열 사용

• 새 데이터를 피벗 테이블의 행, 열 또는 필터에 표시하거나 Power View 시각화의 축, 범례 또는 타일 BY에 표시하려면 계산된 열을 사용해야 합니다. 일반 데이터 열과 마찬가지로 계산된 열은 모든 영역에서 필드로 사용할 수 있으며 숫자인 경우 VALUES로 집계할 수도 있습니다.
• 새 데이터를 행에 대한 고정 값으로 하려는 경우. 예를 들어 날짜 열이 있는 날짜 테이블이 있고 월 숫자만 포함하는 다른 열이 필요합니다. 날짜 열의 날짜를 기준으로 월 번호만 계산하는 계산 열을 만들 수 있습니다. 예: =MONTH('Date'[Date]).
• 테이블의 각 행에 대한 텍스트 값을 추가하려면 계산된 열을 사용합니다. 텍스트 값이 있는 필드는 VALUES로 집계할 수 없습니다. 예를 들어 =FORMAT('Date',[Date],"mmmm")은 날짜 테이블의 날짜 열에 있는 각 날짜의 월 이름을 제공합니다.

측정값 사용

• 계산 결과가 항상 피벗 테이블에서 선택한 다른 필드에 종속되는 경우.
• 일종의 필터를 기반으로 개수를 계산하거나 전년 대비 분산 계산과 같이 더 복잡한 계산을 수행해야 하는 경우 계산 필드를 사용합니다.
• 통합 문서 크기를 최소값으로 유지하고 성능을 최대화하려면 가능한 한 많은 계산을 측정값으로 만듭니다. 대부분의 경우 모든 계산을 측정값으로 사용할 수 있어 통합 문서 크기를 크게 줄이고 새로 고침 시간을 단축할 수 있습니다.

수익 열을 사용할 때처럼 계산된 열을 만든 다음 피벗 테이블 또는 보고서에서 집계하는 것은 잘못된 것이 아니라는 점을 기억하세요. 실제로 자신만의 계산에 대해 배우고 만들 수 있는 정말 훌륭하고 쉬운 방법입니다. 파워 피벗의 이러한 두 가지 매우 강력한 기능에 대한 이해가 깊어질수록 가능한 한 가장 효율적이고 정확한 데이터 모델을 만들고 싶을 것입니다. 여기서 배운 내용이 도움이 되었기를 바랍니다. 당신을 도울 수 있는 다른 정말 훌륭한 리소스도 있습니다. 다음은 DAX 수식의 컨텍스트, Power Pivot의 집계 및 DAX 리소스 센터입니다. 회계 및 재무 전문가를 대상으로 하는 좀 더 고급된 Excel의 Microsoft Power Pivot을 사용한 손익 데이터 모델링 및 분석 샘플에는 훌륭한 데이터 모델링 및 수식 예제가 포함되어 있습니다.