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

Hi Anonymous,

AFAIK, you can use measure expression to interact with filter selection and return category tags based on current value, but measures cannot be used on axis or legend fields. (current they only support columns)
For this scenario, I'd like to suggest you use table visuals instead to show the record with measure expression results of categories.

BTW, calculated column/table formulas are not able to dynamically change based on slicer/filters. (they are working on different data levels and you can't use child level to affect its parent)

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng