Power BI DAX Patterns 백분위수 계산 (Percentile Calculation)

백분위수(Percentile)는 값들을 순서대로 나열했을 때 백분율로 나타낸 특정 위치의 값을 이르는 용어입니다. 크기가 작은 것부터 나열하였을 때 가장 작은 것을 0, 가장 큰 것을 100이 됩니다. Power BI에서 백분위수를 계산하는 방법을 정리하겠습니다.

 

결과

• ‘직책별 백분위수’ 열 : 같은 직책 내에서 각 직원 급여의 백분위수를 구한 값입니다.
• ‘직책별 백분위수 순위’ 열 : 같은 직책 내에서 각 직원 급여의 백분위수를 기준으로 순위를 구한 값입니다.
• ‘전체 백분위수’ 열 : 모든 직책 내에서 각 직원 급여의 백분위수를 구한 값입니다.
• ‘전체 백분위수 순위’ 열 : 모든 직책 내에서 각 직원 급여의 백분위수를 기준으로 순위를 구한 값입니다.

백분위수 결과

 

측정식

급여에 대한 합을 구하는 측정식입니다.

급여 = SUM('Data Table'[Salary])

 

 

같은 직책 내에서 각 직원 급여의 백분위수를 구하는 측정식입니다.

• VAR절 : 급여합에 대한 변수를 선언합니다.
• IF절 : ‘Id No’가 단일 값일 때 후속 측정식을 수행하고 아니면 0을 반환합니다.
• COALESCE : DIVIDE절을 수행합니다.
• DIVIDE절 : 두개의 CALCULATE 절을 나눕니다.
• 첫 번째 CALCULATE 절 : 같은 직책의 ‘Data Table’에서 해당하는 행의 급여보다 작은 데이터 행의 개수를 구합니다.
• 두 번째 CALCULATE 절 : 같은 직책의 모든 데이터 행의 개수를 구합니다.

직책별 백분위수 = 
VAR TotalSal = 
    [급여]
RETURN
    IF(
        HASONEVALUE('Data Table'[사번]),
        COALESCE(
            DIVIDE(
                CALCULATE(
                    COUNTROWS('Data Table'),
                    FILTER(
                        ALLEXCEPT('Data Table','Data Table'[직책]),
                        'Data Table'[Salary] < TotalSal
                    )
                ),
                CALCULATE(
                    COUNTROWS('Data Table'),
                    ALLEXCEPT('Data Table','Data Table'[직책])
                )
            ),
            0
        )
    )

 

 

같은 직책 내에서 각 직원 급여의 백분위수에 대한 순위를 구하는 측정식입니다.

• IF절 : ‘Id No’가 단일 값일 때 후속 측정식을 수행합니다.
• RANKX절 : 같은 직책 내에서 백분위수를 기준으로 순위를 구합니다. DESC는 내림차순으로 정렬합니다. Dense는 동률 다음 순위는 다음 순위 값을 반영합니다.

직책별 백분위수 순위 = 
IF(
    HASONEVALUE('Data Table'[사번]),
    RANKX(
        ALLEXCEPT('Data Table','Data Table'[직책]),
        [직책별 백분위수],,
        DESC,
        Dense
    )
)

 

 

모든 직책내에서 각 직원 급여의 백분위수를 구하는 측정식입니다.

• ‘직책별 백분위수’ 측정식 대비하여 두 CALULATE절에서 ALLEXCEPT(‘Data Table’, ‘Data Table’[직책]) 대신에 ALL(‘Data Table’)이 반영된 것 외에는 동일합니다.

전체 백분위수 = 
VAR SalaryValue = 
    [급여]
RETURN
    IF(
        HASONEVALUE('Data Table'[사번]),
        COALESCE(
            DIVIDE(
                CALCULATE(
                    COUNTROWS('Data Table'),
                    FILTER(
                        ALL('Data Table'),
                        'Data Table'[Salary] < SalaryValue
                    )
                ),
                CALCULATE(
                    COUNTROWS('Data Table'),
                    ALL('Data Table')
                )
            ),
            0
        )
    )

 

 

모든 직책내에서 각 직원 급여의 백분위수에 대한 순위를 구하는 측정식입니다.

• IF절 : ‘Id No’가 단일 값일 때 후속 측정식을 수행합니다.
• RANKX절 : 모든 테이블에서 ‘전체 백분위수’을 기준으로 순위를 구합니다. DESC는 내림차순으로 정렬합니다. Dense는 동률 다음 순위는 다음 순위 값을 반영합니다.

전체 백분위수 순위 = 
IF(
    HASONEVALUE('Data Table'[사번]),
    RANKX(
        ALL('Data Table'),
        [전체 백분위수],,
        DESC,
        Dense
    )
)

 

정리 소감

급여 자체에 대한 백분위가 아닌 구성원에 대한 백분위수를 구할 수 있었습니다. 점수에 대한 비교가 아닌 구성원 내에서 상대 비교를 하는데 유용한 분석인 것 같습니다. 또한 ALLEXCEPT와 ALL을 구분하여 사용하면 전체 또는 하위 그룹에 대한 비교를 할 수 있음을 알 수 있었습니다.