Dynamic quartile calculations using measures

[mmcblues]

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

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.

Current Result

Expected Result as per excel

[mmcblues · Accepted solution]

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

[Anonymous]

Hi @mmcblues,

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

[VahidDM]

Hi @mmcblues 

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/1572...

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  !!

[mmcblues]

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

[Anonymous]

Hi @mmcblues,

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