How to get a percentile/quartile when I have a measure with the values to measure and not a table

[Anonymous]

Hello Everyone, 

I want to calculate the percentile or quartile of an item. I used a formula posted in this forum and it works however, I need to modify it since my values are not stored in a table but in a measure.

See below formula :

*In the below example, I need to modify the formula to successfully substitute the column reference of  'Sales'[SalesAmount] withmy measure called [SalesAmount] (Where I have the values stored to help me in the calculation of percentile/quartile)

Quartile =

VAR SelectedUnit =

SUM ( 'Sales'[SalesAmount])

VAR p25 =

PERCENTILEX.INC (

ALLSELECTED ( 'Item'[Item Number] ),

CALCULATE ( SUM ( 'Sales'[SalesAmount]) ),

0.25

)

VAR p50 =

PERCENTILEX.INC (

ALLSELECTED ( 'Item'[Item Number] ),

CALCULATE ( SUM ( 'Sales'[SalesAmount] ) ),

0.5

)

VAR p75 =

PERCENTILEX.INC (

ALLSELECTED ( 'Item'[Item Number] ),

CALCULATE ( SUM ( 'Sales'[SalesAmount]) ),

0.75

)

RETURN

SWITCH (

ISFILTERED('Item'[Item Number]),

SelectedUnit <= p25, "Low",

SelectedUnit > p25

&& SelectedUnit <= p50, "Mid",

"High"

)

Thank you in advance for your help!

[v-xiaosun-msft]

Hi @Anonymous ,

You can try to use SUMX funtion to replace SUM function in your formula as below. Here, measure is  your measure called [SalesAmount] .

SUMX('Sales','Sales'[Measure] )

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.