Power Pivot의 집계

집계는 데이터를 축소, 요약 또는 그룹화하는 방법입니다. 테이블 또는 다른 데이터 원본의 원시 데이터로 시작하면 데이터는 종종 평평합니다. 즉, 세부 정보가 많지만 어떤 방식으로든 구성되거나 그룹화되지 않았습니다. 이렇게 요약 또는 구조가 부족하면 데이터에서 패턴을 검색하기가 어려울 수 있습니다. 데이터 모델링의 중요한 부분은 특정 비즈니스 질문에 대한 답변으로 패턴을 단순화, 추상화 또는 요약하는 집계를 정의하는 것입니다.

AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN 또는 SUM을 사용하는 집계와 같은 가장 일반적인 집계는 AutoSum을 사용하여 측정값에서 자동으로 만들 수 있습니다. AVERAGEX, COUNTX, COUNTROWS 또는 SUMX와 같은 다른 유형의 집계는 테이블을 반환하며 DAX(데이터 분석 식)를 사용하여 만든 수식이 필요합니다.

파워 피벗 집계 이해

집계에 대한 그룹 선택

데이터를 집계할 때 제품, 가격, 지역 또는 날짜와 같은 특성을 기준으로 데이터를 그룹화한 다음 그룹의 모든 데이터에 대해 작동하는 수식을 정의합니다. 예를 들어 1년 동안 합계를 만들 때 집계를 만듭니다. 그런 다음 전년도에 비해 올해의 비율을 만들고 백분율로 제시하는 경우 다른 유형의 집계입니다.

데이터 그룹화 방법에 대한 결정은 비즈니스 질문에 의해 결정됩니다. 예를 들어 집계는 다음 질문에 답변할 수 있습니다.

Counts 한 달에 몇 개의 트랜잭션이 있었나요?

평균 영업 사원이 이번 달의 평균 판매액은 어떻게 되었나요?

최소 및 최대값 판매 단위 측면에서 상위 5위 안에는 어떤 판매 지구가 있나요?

이러한 질문에 답변하는 계산을 만들려면 개수 또는 합계를 포함하는 자세한 데이터가 있어야 하며, 숫자 데이터는 결과를 구성하는 데 사용할 그룹과 어떤 식으로든 관련이 있어야 합니다.

데이터에 제품 범주 또는 저장소가 있는 지리적 영역의 이름과 같이 그룹화에 사용할 수 있는 값이 아직 포함되어 있지 않은 경우 범주를 추가하여 데이터에 그룹을 도입할 수 있습니다. Excel에서 그룹을 빌드할 때 워크시트의 열 중에서 사용할 그룹을 수동으로 입력하거나 선택해야 합니다. 그러나 관계형 시스템에서는 제품의 범주와 같은 계층 구조가 팩트 또는 값 테이블과 다른 테이블에 저장되는 경우가 많습니다. 일반적으로 범주 테이블은 일종의 키로 팩트 데이터에 연결됩니다. 예를 들어 데이터에 제품 ID가 포함되어 있지만 제품 이름이나 범주는 포함하지 않는다고 가정해 보겠습니다. 범주를 플랫 Excel 워크시트에 추가하려면 범주 이름이 포함된 열을 복사해야 합니다. 파워 피벗 를 사용하면 제품 범주 테이블을 데이터 모델로 가져오고 숫자 데이터와 제품 범주 목록을 사용하여 테이블 간에 관계를 만든 다음 범주를 사용하여 데이터를 그룹화할 수 있습니다. 자세한 내용은 테이블 간의 관계 Create 참조하세요.

집계에 대한 함수 선택

사용할 그룹을 식별하고 추가한 후에는 집계에 사용할 수학적 함수를 결정해야 합니다. 집계라는 단어는 합계, 평균, 최소값 또는 개수와 같은 집계에 사용되는 수학 또는 통계 연산의 동의어로 사용되는 경우가 많습니다. 그러나 파워 피벗파워 피벗 Excel에 있는 표준 집계 외에도 집계를 위한 사용자 지정 수식을 만들 수 있습니다.

예를 들어 이전 예제에서 사용된 것과 동일한 값 및 그룹화 집합을 고려할 때 다음 질문에 대답하는 사용자 지정 집계를 만들 수 있습니다.

필터링된 개수 월말 유지 관리 기간을 제외하고 한 달에 몇 개의 트랜잭션이 있었나요?

시간 경과에 따른 평균을 사용하는 비율 작년 같은 기간에 비해 매출 증가율이나 감소율은 어땠나요?

그룹화된 최소값 및 최대값 각 제품 범주 또는 각 판매 프로모션에서 상위에 오른 판매 지구는 무엇입니까?

수식 및 피벗 테이블에 집계 추가

데이터를 의미 있게 그룹화해야 하는 방법과 작업하려는 값에 대한 일반적인 아이디어가 있는 경우 피벗 테이블을 작성할지 아니면 테이블 내에서 계산을 만들 것인지 결정할 수 있습니다. 파워 피벗 합계, 개수 또는 평균과 같은 집계를 만드는 Excel의 기본 기능을 확장하고 향상시킵니다. 파워 피벗 창 또는 Excel 피벗 테이블 영역 내에서 파워 피벗 사용자 지정 집계를 만들 수 있습니다.

계산 열에서 현재 행 컨텍스트를 고려하여 다른 테이블에서 관련 행을 검색한 다음 관련 행에서 해당 값을 합산, 개수 또는 평균으로 계산하는 집계를 만들 수 있습니다.
측정값에서는 수식 내에 정의된 필터와 피벗 테이블의 디자인과 슬라이서, 열 머리글 및 행 머리글의 선택에 의해 적용되는 필터를 모두 사용하는 동적 집계를 만들 수 있습니다. 표준 집계를 사용하는 측정값은 자동 합계를 사용하거나 수식을 만들어 파워 피벗 만들 수 있습니다. Excel의 피벗 테이블에서 표준 집계를 사용하여 암시적 측정값을 만들 수도 있습니다.

피벗 테이블에 그룹화 추가

피벗 테이블을 디자인할 때 그룹화, 범주 또는 계층 구조를 나타내는 필드를 피벗 테이블의 열 및 행 섹션으로 끌어 데이터를 그룹화합니다. 그런 다음 숫자 값이 포함된 필드를 값 영역으로 끌어서 계산, 평균 또는 합계할 수 있습니다.

피벗 테이블에 범주를 추가하지만 범주 데이터가 팩트 데이터와 관련이 없는 경우 오류 또는 특이한 결과가 발생할 수 있습니다. 일반적으로 파워 피벗 자동으로 관계를 검색하고 제안하여 문제를 해결하려고 합니다. 자세한 내용은 피벗 테이블의 관계 작업을 참조하세요.

필드를 슬라이서로 끌어서 볼 특정 데이터 그룹을 선택할 수도 있습니다. 슬라이서를 사용하면 피벗 테이블에서 결과를 대화형으로 그룹화, 정렬 및 필터링할 수 있습니다.

수식에서 그룹화 작업

그룹화 및 범주를 사용하여 테이블 간의 관계를 만든 다음, 이러한 관계를 활용하여 관련 값을 조회하는 수식을 만들어 테이블에 저장된 데이터를 집계할 수도 있습니다.

즉, 값을 범주별로 그룹화하는 수식을 만들려면 먼저 관계를 사용하여 세부 정보 데이터가 포함된 테이블과 범주가 포함된 테이블을 연결한 다음 수식을 빌드합니다.

조회를 사용하는 수식을 만드는 방법에 대한 자세한 내용은 파워 피벗 수식의 조회를 참조하세요.

집계에서 필터 사용

파워 피벗 새로운 기능은 사용자 인터페이스 및 피벗 테이블 또는 차트 내에서뿐만 아니라 집계를 계산하는 데 사용하는 바로 그 수식에서 데이터 열과 테이블에 필터를 적용하는 기능입니다. 필터는 계산 열과 의 수식 모두에서 사용할 수 있습니다.

예를 들어 새 DAX 집계 함수에서 합계 또는 개수를 계산할 값을 지정하는 대신 전체 테이블을 인수로 지정할 수 있습니다. 해당 테이블에 필터를 적용하지 않은 경우 집계 함수는 테이블의 지정된 열에 있는 모든 값에 대해 작동합니다. 그러나 DAX에서는 테이블에 동적 또는 정적 필터를 만들 수 있으므로 집계가 필터 조건 및 현재 컨텍스트에 따라 다른 데이터 하위 집합에 대해 작동합니다.

수식에 조건과 필터를 결합하여 수식에 제공된 값에 따라 변경되거나 피벗 테이블의 행 머리글 및 열 머리글 선택에 따라 변경되는 집계를 만들 수 있습니다.

자세한 내용은 수식에서 데이터 필터링을 참조하세요.

Excel 집계 함수 및 DAX 집계 함수 비교

다음 표에서는 Excel에서 제공하는 표준 집계 함수 중 일부를 나열하고 파워 피벗 에서 이러한 함수의 구현에 대한 링크를 제공합니다. 이러한 함수의 DAX 버전은 Excel 버전과 거의 동일하게 작동하며, 구문과 특정 데이터 형식의 처리에는 약간의 차이가 있습니다.

표준 집계 함수

함수	와일드카드 문자
평균	열에 있는 모든 숫자의 평균(산술 평균)을 반환합니다.
AVERAGEA	열에 있는 모든 값의 평균(산술 평균)을 반환합니다. 텍스트 및 숫자가 아닌 값을 처리합니다.
횟수	열의 숫자 값 수를 계산합니다.
Counta	비어 있지 않은 열의 값 수를 계산합니다.
최대	열에서 가장 큰 숫자 값을 반환합니다.
맥스	테이블에 대해 계산된 식 집합에서 가장 큰 값을 반환합니다.
분	열에서 가장 작은 숫자 값을 반환합니다.
MINX	테이블에 대해 계산된 식 집합에서 가장 작은 값을 반환합니다.
합계	열의 모든 숫자를 추가합니다.

DAX 집계 함수

DAX에는 집계를 수행할 테이블을 지정할 수 있는 집계 함수가 포함되어 있습니다. 따라서 열의 값을 추가하거나 평균하는 대신 이러한 함수를 사용하면 집계할 데이터를 동적으로 정의하는 식을 만들 수 있습니다.

다음 표에서는 DAX에서 사용할 수 있는 집계 함수를 나열합니다.

함수	와일드카드 문자
Averagex	테이블에 대해 계산된 식 집합의 평균을 계산합니다.
COUNTAX	테이블에 대해 평가된 식 집합을 계산합니다.
COUNTBLANK	열의 빈 값 수를 계산합니다.
COUNTX	테이블의 총 행 수를 계산합니다.
COUNTROWS	필터 함수와 같은 중첩 테이블 함수에서 반환된 행 수를 계산합니다.
SUMX	테이블에 대해 계산된 식 집합의 합계를 반환합니다.

DAX와 Excel 집계 함수의 차이점

이러한 함수는 Excel과 이름이 같지만 파워 피벗 메모리 내 분석 엔진을 활용하고 테이블 및 열 작업을 위해 다시 작성되었습니다. Excel 통합 문서에서는 DAX 수식을 사용할 수 없으며 그 반대의 경우도 마찬가지입니다. 파워 피벗 창과 파워 피벗 데이터를 기반으로 하는 피벗 테이블에서만 사용할 수 있습니다. 또한 함수의 이름은 동일하지만 동작은 약간 다를 수 있습니다. 자세한 내용은 개별 함수 참조 topics 참조하세요.

집계에서 열을 평가하는 방식도 Excel에서 집계를 처리하는 방식과 다릅니다. 예제는 설명하는 데 도움이 될 수 있습니다.

Sales 테이블의 Amount 열에 있는 값의 합계를 가져와서 다음 수식을 만들려는 경우를 가정해 보겠습니다.

=SUM('Sales'[Amount])

가장 간단한 경우 함수는 필터링되지 않은 단일 열에서 값을 가져오고 결과는 Excel에서와 같으며 항상 열의 값인 Amount만 추가합니다. 그러나 파워 피벗 수식은 "Sales 테이블의 각 행에 대해 Amount의 값 가져오기"로 해석된 다음 해당 개별 값을 추가합니다. 파워 피벗 집계가 수행되는 각 행을 평가하고 각 행에 대해 단일 스칼라 값을 계산한 다음 해당 값에 대한 집계를 수행합니다. 따라서 필터가 테이블에 적용되었거나 필터링될 수 있는 다른 집계를 기반으로 값을 계산하는 경우 수식의 결과가 다를 수 있습니다. 자세한 내용은 DAX 수식의 컨텍스트를 참조하세요.

DAX 시간 인텔리전스 함수

이전 섹션에서 설명한 테이블 집계 함수 외에도 DAX에는 기본 제공 시간 인텔리전스를 제공하기 위해 지정한 날짜 및 시간과 함께 작동하는 집계 함수가 있습니다. 이러한 함수는 날짜 범위를 사용하여 관련 값을 얻고 값을 집계합니다. 날짜 범위의 값을 비교할 수도 있습니다.

다음 표에서는 집계에 사용할 수 있는 시간 인텔리전스 함수를 나열합니다.

함수	와일드카드 문자
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	지정된 기간의 달력 끝에 있는 값을 계산합니다.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	지정된 기간 이전 기간의 달력 끝에 있는 값을 계산합니다.
TOTALMTD TOTALYTD TOTALQTD	기간의 첫 날에 시작하여 지정된 날짜 열의 최신 날짜에 끝나는 간격에 대한 값을 계산합니다.

시간 인텔리전스 함수 섹션(시간 인텔리전스 함수)의 다른 함수는 집계에 사용할 날짜 또는 사용자 지정 날짜 범위를 검색하는 데 사용할 수 있는 함수입니다. 예를 들어 DATESINPERIOD 함수를 사용하여 날짜 범위를 반환하고 해당 날짜 집합을 다른 함수에 대한 인수로 사용하여 해당 날짜에 대한 사용자 지정 집계를 계산할 수 있습니다.