Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic quartile calculations using measures

Refer the below dataset, I need to calculate or categorize the value field below in to quartiles. I have a Category slicer in the report , which is a multiselect slicer and when i select a category, ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    I was able to resolve the issue by using below measure.

     

    Count of employees =
    var _p1=CALCULATE(PERCENTILE.INC(TESTDATA[Value],0.25),ALLEXCEPT(TESTDATA,TESTDATA[Value],TESTDATA[Category]))
    var _p2=CALCULATE(PERCENTILE.INC(TESTDATA[Value],0.5),ALLEXCEPT(TESTDATA,TESTDATA[Value],TESTDATA[Category]))
    var _p3=CALCULATE(PERCENTILE.INC(TESTDATA[Value],0.75),ALLEXCEPT(TESTDATA,TESTDATA[Value],TESTDATA[Category]))
    var _p4=CALCULATE(PERCENTILE.INC(TESTDATA[Value],1),ALLEXCEPT(TESTDATA,TESTDATA[Value],TESTDATA[Category]))
    return SWITCH(SELECTEDVALUE(QuartileLabel[Label]),
    "Q1",CALCULATE(DISTINCTCOUNT(TESTDATA[ID]),FILTER(TESTDATA,TESTDATA[Value]<=_p1)),
    "Q2",CALCULATE(DISTINCTCOUNT(TESTDATA[ID]),FILTER(TESTDATA,TESTDATA[Value]>_p1 && TESTDATA[Value]<=_p2)),
    "Q3",CALCULATE(DISTINCTCOUNT(TESTDATA[ID]),FILTER(TESTDATA,TESTDATA[Value]>_p2&& TESTDATA[Value]<=_p3)),
    "Q4",CALCULATE(DISTINCTCOUNT(TESTDATA[ID]),FILTER(TESTDATA,TESTDATA[Value]>_p3&& TESTDATA[Value]<=_p4)))
     
     
VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
5 years ago

Hi Anonymous 

 

Have you seen these links, I think those links would be helpful:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/QUARTILE/m-p/1064307

https://community.powerbi.com/t5/Desktop/How-do-you-slice-data-into-quartiles-and-quintiles/m-p/157262

https://community.powerbi.com/t5/Desktop/Quartile-with-calculated-mearues/m-p/547337

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

 

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

Anonymous's avatar
Anonymous
Not applicable
5 years ago
CategoryGenderIDValue
AM148
AF229
AM339
AM412
AM512
AF639
BF725
BF824
BM921
BM1014
BF1139
CM1213
CM1346
CM1437
CM1534
CF1647
CF1714
CM1817
CM1927
CM2014

Just to update you, i have gone through the links that you posted but its not getting what i wanted.

am using a measure like below

Quartile_Num =

VAR Weights = SUMMARIZE ( ALLSELECTED(TESTDATA), TESTDATA[ID], "Metric",Sum([Value])

VAR UQ1 =PERCENTILEX.INC(Weights, [Metric], 0.25)
VAR UQ2 = PERCENTILEX.INC(Weights, [Metric], 0.50)
VAR UQ3 = PERCENTILEX.INC(Weights, [Metric], 0.75)

VAR CurrW = CALCULATE(Sum([Value])

RETURN
SWITCH (
TRUE (),
CurrW <= UQ1, "Q1",
CurrW > UQ1 && CurrW <= UQ2, "Q2",
CurrW > UQ2 && CurrW <= UQ3, "Q3",
"Q4"
)
 
along with this, am using a disconnected table for Quartile labels.
the issue i have is when i was calculating count for each gender, the percentile calculation is getting recalculated based on that filter and i dont want that. i have only one slicer in my report, that is for category. i want only that filter to affect the percentile calculation