Forum Discussion
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, the quartile calculation should recalculate.
| Category | Gender | ID | Value |
| A | M | 1 | 48 |
| A | F | 2 | 29 |
| A | M | 3 | 39 |
| A | M | 4 | 12 |
| A | M | 5 | 12 |
| A | F | 6 | 39 |
| B | F | 7 | 25 |
| B | F | 8 | 24 |
| B | M | 9 | 21 |
| B | M | 10 | 14 |
| B | F | 11 | 39 |
| C | M | 12 | 13 |
| C | M | 13 | 46 |
| C | M | 14 | 37 |
| C | M | 15 | 34 |
| C | F | 16 | 47 |
| C | F | 17 | 14 |
| C | M | 18 | 17 |
| C | M | 19 | 27 |
| C | M | 20 | 14 |
Just to updatethe question,
am using a measure like below
Expected Result as per excel
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)))i got the reference from below post
https://community.powerbi.com/t5/Desktop/Breaking-down-into-quartiles/m-p/1727028#M681765
5 Replies
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/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 !!
Category Gender ID Value A M 1 48 A F 2 29 A M 3 39 A M 4 12 A M 5 12 A F 6 39 B F 7 25 B F 8 24 B M 9 21 B M 10 14 B F 11 39 C M 12 13 C M 13 46 C M 14 37 C M 15 34 C F 16 47 C F 17 14 C M 18 17 C M 19 27 C M 20 14 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])RETURNSWITCH (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 calculationHi Anonymous,
You can take a look at the following blog about all functions to use them ignore the particular filter effects.
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI
Regards,Xiaoxin Sheng
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
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)))i got the reference from below post
https://community.powerbi.com/t5/Desktop/Breaking-down-into-quartiles/m-p/1727028#M681765
