Quartile with added condition in Power BI

[engingee]

Hi eveyone,

I have a table like in below with blue colour, based on the data in blue table,

Firstly I would like to calcuate the Quartile for each company, so for Q1, I am using formula in excel is:  =QUARTILE(IF($A$1:$A$19=G1,$B$1:$B$19),1)

Q2 is=QUARTILE(IF($A$1:$A$19=G1,$B$1:$B$19),2)

Q3 is=QUARTILE(IF($A$1:$A$19=G1,$B$1:$B$19),3)

so it brings me the result of yellow table

Secondly, based on the result from yellow table, if the company rate less than or equals to Q1, the Level will be Q1;

If the company rate less than or equals to Q2, the level will be Q2;

if the company rate less that or equals to Q3, the level will be Q3;

and if the company rate large than Q3, the level will be Q4.

I would like to have a result for each company according to their rate (Green table, column N) in Power BI, for example

for N2, because it's company C, and its rate is 0.0166, whcih is less than I2, so it is Q1

for N3, because it's company A, and its rate is 0.0382, whcih is large than G4, so it is Q4

I would like to have a complete table such as Green table above with completed Level colunm, but could not figure out how to get this done in PowerBI.

I will appreciate if any one could help with this complex senario. Thanks!

[v-yiruan-msft · Accepted solution]

Hi @engingee ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a level dimension table as below

2. Create two measures as below to get the quartiles by using DAX function PERCENTILEX.INC

Measure = 
PERCENTILEX.INC (
    FILTER ( 'Table', 'Table'[Company] = SELECTEDVALUE ( 'Table'[Company] ) ),
    'Table'[Rate],
    SWITCH (
        SELECTEDVALUE ( 'Quartiles'[Level] ),
        "Q1", 0.25,
        "Q2", 0.5,
        "Q3", 0.75,
        "Q4", 1
    )
)

Quartile = 
SUMX (
    VALUES ( 'Quartiles'[Level] ),
    SUMX ( VALUES ( 'Table'[Company] ), [Measure] )
)

Best Regards

[engingee]

Thanks @v-yiruan-msft ! I will sure double check and once it workes, I will accept as solution.

[amitchandak]

@engingee , check if this can help

https://sqldusty.com/2018/08/31/calculating-quartiles-with-dax-and-power-bi/

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.