Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
idaly
Frequent Visitor

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

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!
1 REPLY 1
v-xiaosun-msft
Community Support
Community Support

Hi @idaly ,

 

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] )

 

vxiaosunmsft_0-1671097538849.png

 

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.