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)))
     
     
Anonymous's avatar
Anonymous
Not applicable
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)))