Calculating percentiles by group in Power BI

Question

Below is a sample data and I am looking for a solution to calculate percentiles (25th, 50th, 75th, 100th) for quantity sold grouped by country.

So basically add countries into different buckets from low, mid 1, mid 2 or high depending upon the unit_quantity. So if I create a table shown below in power bi, I want to create a calculated measure that adds the countries into these different buckets.

Currently, what I have tried is create 3 different quartiles in below dax measure and then using IF function i have tried to put them in different buckets :

Quartile = 
var FirstQ = 
    CALCULATE(
        PERCENTILE.INC('Sample Table'[unit_quantity], .25),
        ALLSELECTED('Sample Table')
    )
var SecondQ = 
    CALCULATE(
        PERCENTILE.INC('Sample Table'[unit_quantity], .5),
        ALLSELECTED('Sample Table')
    )
var ThirdQ = 
    CALCULATE(
        PERCENTILE.INC(Sample Table'[unit_quantity], .75),
        ALLSELECTED(Sample Table')
    )
var currentVal = SELECTEDVALUE(Sample Table'[unit_quantity])
return
 IF(currentVal <= FirstQ, "Low",
        IF(currentVal > FirstQ && currentVal <= SecondQ, "Mid",
            IF(currentVal > SecondQ && currentVal <= ThirdQ, "Mid 2", "High")
    )
)

But the above measure calculates quartiles for the complete table and not grouped by country. Also I want this to be dynamic since I am going to have a slicer for category column so the percentile values should dynamically change according to the category. I am very new to power BI so please bear with me.

Angelo Canepa Angelo Canepa · Accepted Answer · 2021-04-01T07:42:18

You can use PERCENTILEX to run a table calculation, in this case, all countries. I've added a condition of ISFILTERED to only display the results if the country field is present.

Calculation

Quartile = 
VAR SelectedUnit =
    SUM ( 'Table'[unit_quantity] )
VAR p25 =
    PERCENTILEX.INC (
        ALLSELECTED ( 'Table'[country] ),
        CALCULATE ( SUM ( 'Table'[unit_quantity] ) ),
        0.25
    )
VAR p50 =
    PERCENTILEX.INC (
        ALLSELECTED ( 'Table'[country] ),
        CALCULATE ( SUM ( 'Table'[unit_quantity] ) ),
        0.5
    )
VAR p75 =
    PERCENTILEX.INC (
        ALLSELECTED ( 'Table'[country] ),
        CALCULATE ( SUM ( 'Table'[unit_quantity] ) ),
        0.75
    )
RETURN
    SWITCH (
        ISFILTERED('Table'[country]),
        SelectedUnit <= p25, "Low",
        SelectedUnit > p25
            && SelectedUnit <= p50, "Mid",
        "High"
    )

Output

country	Unit Sum	Quartile
Bulgaria	2	Low
Canada	83	High
Croatia	49	Mid
India	75	High
Russia	38	Low
United States	69	High

Calculating percentiles by group in Power BI

1 Answers

Calculation

Output