Power BI DAX Pattern - 누적합계 구하는 방법 (Cumulative total)

지난번 포스팅에서 코로나 확진자의 이동평균을 구했습니다. 코로나 발병 후 현재까지 누적 확진자도 구해볼 수 있지 않을까요? Cumulative total이라고도 불리는 누적합계를 Power BI에서 구하는 DAX 식에 대해서 살펴보겠습니다.

 

결과

기간은 21년 9월 부터 22년 3월 5일까지 설정했습니다. 모든 기간과 선택기간과 차이를 보기 위해서 임의로 설정해 본 것입니다. 시작 날짜부터 설정하면 모든 기간 누적합계와 선택기간 누적합계가 동일하게 나오기 때문입니다.

 

차트를 보시면 녹색은 일일확진자 그래프입니다. 노란색은 선택기간의 누적합계입니다. 시작이 일일 확진자와 일치합니다. 선택한 기간에 대한 누적합계가 제대로 표현되고 있네요. 반면에 파란색은 모든 기간에 대한 누적합계입니다. 차트의 시작이 위로 올라가 있습니다. 선택한 기간 앞의 값들이 누적 합계되어 반영되었기 때문입니다. 22년에 들어서서 확진자 폭증에 따라 누적합계가 급증하는 것이 확연하게 보입니다.

 

오른쪽 테이블을 보시면 일자, 일일 확진자, 모든 기간에 대한 누적합계, 선택한 기간에 대한 누적합계를 테이블 형식으로 볼 수 있습니다.

누적확진자 대시보드

 

누적합계 DAX Pattern

모든 기간 누적 합계 (ALL)

먼저 일일 확진자를 합하는 측정식을 만들어 줍니다.

일일확진자 = SUM('Sheet1'[new_cases])

아래 코드가 모든 기간에 대한 누적합계를 구하는 DAX 식입니다.여기서 핵심 부분은 CALCULATE이하 절입니다. 모든 일자에 대해서, 날짜 컨텍스트의 앞의 기간 동안 [일일 확진자]를 계산하게 됩니다. 위에 FirstVisibleDate, LastDateWithNewCases를 반영한 이유는 실제 데이터 테이블에 있는 기간 동안만 시각화 요소에 반영하기 위함입니다. 없어도 동작하는데 큰 무리는 없습니다.

누적합계_All =
VAR LastVisibleDate =
    MAX ( 'Calendar'[Date] )
VAR FirstVisibleDate =
    MIN ( 'Calendar'[Date] )
VAR LastDateWithNewCases =
    CALCULATE ( MAX ( 'Sheet1'[date] ), REMOVEFILTERS () )
VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithNewCases,
        CALCULATE (
            [일일확진자],
            FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= LastVisibleDate )
        )
    )
RETURN
    Result

 

선택 기간 누적 합계 (ALLSELECTED)

아래 코드는 선택한 기간에 대해서 누적합계를 구하는 DAX 식입니다. 필터 절에서 ALL('Calendar'[Date])가 ALLSELECTED('Calendar'[Date])로 바뀌었음을 볼 수 있습니다.

누적합계_Selected =
VAR LastVisibleDate =
    MAX ( 'Calendar'[Date] )
VAR FirstVisibleDate =
    MIN ( 'Calendar'[Date] )
VAR LastDateWithNewCases =
    CALCULATE ( MAX ( 'Sheet1'[date] ), REMOVEFILTERS () )
VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithNewCases,
        CALCULATE (
            [일일확진자],
            FILTER ( ALLSELECTED ( 'Calendar'[Date] ), 'Calendar'[Date] <= LastVisibleDate )
        )
    )
RETURN
    Result

ALLSELECTED는 선택된 모든 구간에 대해서 필터를 제거하는 함수입니다. ALLSELECTED에 대한 추가 설명은 링크 참조하시기 바랍니다.

Power BI DAX 기초 - ALL, ALLEXCEPT, ALLSELECTED

Power BI DAX 기초 - ALL, ALLEXCEPT, ALLSELECTED

 

정리 소감

개별적으로 학습했던 ALL, ALLSELECTED, FILTER, CALCULATE들의 조합으로 누적합계를 구할 수 있었습니다. 이렇게 활용되는구나 이해하게 되네요. 누적 합계를 구하고자 할 때 위에서 알아본 DAX Pattern을 기반으로 상황에 맞게 수정하면 효율적으로 데이터 분석을 할 수 있겠습니다.