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