Reply
Frequent Visitor
Posts: 3
Registered: Thursday

Percentile Calculation by passing range of values from measure

Below is the table with year and corresponding volume on Global level

 

Year

Total Volume

2016

182

2017

173

2018

0

 

Another table with volume based on region and Column - Value in this table is calculated as NACA Volume/Total Volume * 100 (I am calculating this in a measure)

 

Year

NACA Volume

Value

2016

14

7.7

2017

10

5.8

 

Now I need to calculate the result as below, percentile should be 10th percentile

 

Region

Percentile

NACA

5.99

 

Formula for Percentile should be PERCENTILE.INC(Value,0.1) i.e, PERCENTILE.INC(7.7:5.8,0.1) = 5.99

 

I could not pass the range of values which is calculated in a measure into percentile function in DAX.

Please help me to calculate this

Regular Visitor
Posts: 45
Registered: ‎05-30-2016

Re: Percentile Calculation by passing range of values from measure

[ Edited ]

Hi dinesh,

In order to achieve your goal you should to use calculated columns instead Value measure.
See these images and tell us if work for you.

 

PercentileCC.pngCalculated Column

Percentile.pngPercentile measure

Frequent Visitor
Posts: 3
Registered: Thursday

Re: Percentile Calculation by passing range of values from measure

Hi Miltinho,

 

Thank you for your response. I cannot use calculated column because, I have shown only the aggregated data in example. In Actual data I have records as below. So I need percentile for Regionwise i.e NACA, and I need to slice by year and percentile should chanage accordingly.

 

Product IDYearRegionVolume
4572016NACA6
3682016NACA3
8572016NACA5
3782016US68
1232016US100
3452017NACA6
7892017NACA4
2562017US163
Frequent Visitor
Posts: 3
Registered: Thursday

Re: Percentile Calculation by passing range of values from measure

Also we need to calculate Value as NACA Volume/Total Volume * 100 on top of that we need to calculate Percentile

Regular Visitor
Posts: 45
Registered: ‎05-30-2016

Re: Percentile Calculation by passing range of values from measure

Ok dinesh,

Try this...

Create a new table:

1-NewTable.png

 

Create their relationship (Both directions):

2-NewRelationship.png

 

Check slicers:

3-Slicer.png