- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Percentile Calculation by passing range of values from measure
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Thursday
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
Re: Percentile Calculation by passing range of values from measure
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Thursday - last edited Thursday
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.
Re: Percentile Calculation by passing range of values from measure
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Friday
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 |
Re: Percentile Calculation by passing range of values from measure
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Friday
Also we need to calculate Value as NACA Volume/Total Volume * 100 on top of that we need to calculate Percentile
Re: Percentile Calculation by passing range of values from measure
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Friday
Ok dinesh,
Try this...
Create a new table:
Create their relationship (Both directions):
Check slicers: