Percentile Calculation by passing range of values from measure

[Anonymous]

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

[Anonymous]

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.

Calculated Column

Percentile measure

[Anonymous]

Ok dinesh,

Try this...

Create a new table:

Create their relationship (Both directions):

Check slicers:

[Anonymous]

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 ID	Year	Region	Volume
457	2016	NACA	6
368	2016	NACA	3
857	2016	NACA	5
378	2016	US	68
123	2016	US	100
345	2017	NACA	6
789	2017	NACA	4
256	2017	US	163

[Anonymous]

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