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, ...
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
| 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])
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
Hi 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