CODEWITHSUNDEEP
powerbidax
iLoveYou3000
iLoveYou3000

Reputation: 57

Calculating percentiles by group in Power BI

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.

enter image description here

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.

enter image description here

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.

Upvotes: 1

Views: 11289

Answers (1)

Angelo Canepa
Angelo Canepa

Reputation: 1781

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

Upvotes: 2

Related Questions