cancel
Showing results for
Did you mean:
Highlighted Microsoft

## Display specific percentiles in a table

My goal is to create this table for my Age Data:

 Percentile Age 50 75 90 95 99 99.9 99.99

My data is in the form of Age and the count for that age bucket.  I already have a CumulativePercent measure to calculate the percentile for each age bucket, so I know Age0 = 60.82%, Age1=66.14%, Age2=69.59% ... Age90 = 99.01% ... all the way to the max age = 100%.  This is nicely displayed in a curved percentile line chart (Age is the x-axis, Percentile is the y-axis).  However, the users of this report care specifically about the percentiles I listed above so I would like to create a table to show a quick summary (instead of hunting for those percentiles on the graph, which has a very long tail).  I created a PercentilesToDisplay table with the column Percentile containing those values listed above.  I just can't figure out how to put all the parts together correctly.

Basically, in psuedocode, I would like a measure that gets the following:

AgeAtXPtile = MAX(AgeData[Age]) WHERE AgeData[CumulativePercent] <= PercentilesToDisplay[Percentile]

I have been playing around with CALCULATE, FILTER, MAXX but have not been able to get the results I want yet.  I'm sure this solution exists I just have not been able to find it! Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support

## Re: Display specific percentiles in a table

We can create three columns and four measures to meet your requirement.

1. First delete the relationship between two tables. 2. Create three columns of percentages separately.

``````A % =
var x = CALCULATE(
SUM('AgeData'[Count]),
FILTER(
'AgeData',
AgeData[Category] = "A"&&
AgeData[Age]<=EARLIER(AgeData[Age])
)
)
var y = CALCULATE(SUM(AgeData[Count]),FILTER(ALLSELECTED(AgeData),AgeData[Category]="A"))
return
DIVIDE(x,y)``````

``````B % =
var x = CALCULATE(
SUM('AgeData'[Count]),
FILTER(
'AgeData',
AgeData[Category] = "B"&&
AgeData[Age]<=EARLIER(AgeData[Age])
)
)
var y = CALCULATE(SUM(AgeData[Count]),FILTER(ALLSELECTED(AgeData),AgeData[Category]="B"))
return
DIVIDE(x,y)``````

``````C % =
var x = CALCULATE(
SUM('AgeData'[Count]),
FILTER(
'AgeData',
AgeData[Category] = "C"&&
AgeData[Age]<=EARLIER(AgeData[Age])
)
)
var y = CALCULATE(SUM(AgeData[Count]),FILTER(ALLSELECTED(AgeData),AgeData[Category]="C"))
return
DIVIDE(x,y)`````` 3. Then we can create four measures to get A, B, C and All.

``A = CALCULATE(MIN(AgeData[Age]),FILTER(AgeData,AgeData[A %]>=MAX(PercentilesToDisplay[Percentile])))``

``B = CALCULATE(MIN(AgeData[Age]),FILTER(AgeData,AgeData[B %]>=MAX(PercentilesToDisplay[Percentile])))``

``C = CALCULATE(MIN(AgeData[Age]),FILTER(AgeData,AgeData[C %]>=MAX(PercentilesToDisplay[Percentile])))``

``All = CALCULATE(MIN(AgeData[Age]),FILTER(AgeData,AgeData[%]>=MAX(PercentilesToDisplay[Percentile])))``

Put them to values and not add category to column, the result like this, If you have any question, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

9 REPLIES 9
Highlighted Community Support

## Re: Display specific percentiles in a table

Sorry for that we don’t understand what is your desire result clearly.

Maybe you can try a measure like this,

``Measure = CALCULATE(MAX(‘AgeData’[Age]),FILTER('AgeData', [CumulativePercent]<=[Percentile]))                                       `` If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Best regards,

Community Support Team _ zhenbw

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

Highlighted Microsoft

## Re: Display specific percentiles in a table

I created a sample data file https://microsoft-my.sharepoint-df.com/:u:/p/cabadart/EXNoRVG-Qu1JlIRN_WPDHOEBEA9OBO9eP8cwwFHhqgg6UA...

I tried the measure you provided but it does not match the expected results:

 Percentile Measure Expected (true) value 50% 229 2 75% 237 12 90% 239 32 95% 239 48 99% 239 117 99.9% 239 209 99.99% 239 236

One added layer of complexity that I did not mention in my original post is that there are 3 different Categories in this data.  I would like this to be calculated as a measure so that it is updated when users select different combinations of the Categories to display.  Thank you!

Highlighted Community Support

## Re: Display specific percentiles in a table

We need to create a calculate table, then create a measure to meet your requirement.

1. Create a calculate table.

``````Table =
SUMMARIZE ( AgeData, AgeData[Age], "C", SUM ( AgeData[Count] ) ),
"Total", CALCULATE ( SUM ( 'AgeData'[Count] ), ALL ( 'AgeData'[Age] ) ),
"Cumlative", CALCULATE (
SUM ( AgeData[Count] ),
FILTER ( AgeData, AgeData[Age] <= EARLIER ( AgeData[Age] ) )
)
),
"Percentage", DIVIDE ( [Cumlative], [Total] )
)`````` 2. Then we can create a measure like this,

``````AgeAtXPtile =
var x = MAX(PercentilesToDisplay[Percentile])
return
CALCULATE(MIN('Table'[Age]),FILTER('Table','Table'[Percentage]>=x))``````

The result like this, If you have any question, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

Highlighted Microsoft

## Re: Display specific percentiles in a table

Thanks we are getting close!  However, it does not seem to work for calculating the percentiles across each different Category, it just shows the same thing for each one: Highlighted Community Support

## Re: Display specific percentiles in a table

Sorry for that we don't understand what is the percentiles, what is the logic of its calculation?

So we create a measure and calculate the each category count as a percentage of the total count.

1. We need to create an index column in PercentilesToDisplay table and create a new column to get the next row percentage. ``````Column =
var x = [Index]+1
var y = CALCULATE(SUM([Percentile]),FILTER('PercentilesToDisplay',PercentilesToDisplay[Index]=x))
return
IF(ISBLANK(y),1,y)`````` 2. Then we can create a measure like this,

``````Age Count =
var x = MAX(PercentilesToDisplay[Percentile])
var y = MAX('PercentilesToDisplay'[Column])
return
CALCULATE(COUNT('AgeData'[Age]),FILTER('Table','Table'[Percentage]>=x && 'Table'[Percentage]<y)) /
CALCULATE(COUNT('AgeData'[Age]),FILTER('Table','Table'[Percentage]>=x && 'Table'[Percentage]<y),ALLSELECTED(AgeData))``````

The result like the following, If it doesn’t meet your requirement, could you please show us the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

Highlighted Microsoft

## Re: Display specific percentiles in a table

By Percentile I mean, X% are Age N are younger.  So the values displayed in the table should be different Ages (not %s, I am not sure what your updated table is showing).  The method you gave me in your previous post ignores any filtering I do by Category.  When the user selects one category, or filters them in any way (like Age < 100), I would like the Key Percentiles table to update dynamically.  I was able to produce the key percentiles by category in Excel like this: So your first method calculated the percentiles for All correctly, but not for any filtering

Highlighted Community Support

## Re: Display specific percentiles in a table

We can create a secondary column and link two tables based on the secondary column to meet your requirement.

1. Create a CumulativePercent column in Age table. Then create a secondary column based on CumulativePercent column.

``````% =
var x = CALCULATE(
SUM('AgeData'[Count]),
FILTER(
'AgeData',
AgeData[Age]<=EARLIER(AgeData[Age])
)
)
var y = SUM(AgeData[Count])
return
DIVIDE(x,y)``````

``````Column =
SWITCH(
TRUE(),
'AgeData'[%]<0.5,0,
'AgeData'[%]>=0.5 && 'AgeData'[%]<0.75,0.5,
'AgeData'[%]>=0.75 && 'AgeData'[%]<0.9,0.75,
'AgeData'[%]>=0.9 && 'AgeData'[%]<0.95,0.9,
'AgeData'[%]>=0.95 && 'AgeData'[%]<0.99,0.95,
'AgeData'[%]>=0.99 && 'AgeData'[%]<0.999,0.99,
'AgeData'[%]>=0.999 && 'AgeData'[%]<0.9999,0.999,0.9999
)`````` 2. Then we can create a relationship between Age table and PercentilesToDisplay table. 3. At last we can create a simple measure,

``AgeAtXPtile = CALCULATE(MIN(AgeData[Age]))``  If it doesn’t meet your requirement, could you tell us what is your screenshot calculate logic?

When the percentile is 50%, why A’s age is 1, B’s age is 9, C’s age is 0?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

Highlighted Microsoft

## Re: Display specific percentiles in a table

This isn't quite it either, sorry if I wasn't explaining it well!

For the total, unfiltered values (A, B and C selected), we have it right - take the sum of counts that are that age or younger divided by the sum of counts for all the ages.

When a specific category is selected, the calculation should be the sum of counts in that category that are that age or younger divided by the sum of all the counts in that category.  Similarly, if A & B are selected, the percentiles would change to sum of all the counts of A & B

I'll attach the excel sheet if that would help show what I mean.  The Raw Data is the same as what went into the Power BI file, and then I created a Pivot table to do the Percentile calculations.

percentilessampledata.xlsx

Highlighted Community Support

## Re: Display specific percentiles in a table

We can create three columns and four measures to meet your requirement.

1. First delete the relationship between two tables. 2. Create three columns of percentages separately.

``````A % =
var x = CALCULATE(
SUM('AgeData'[Count]),
FILTER(
'AgeData',
AgeData[Category] = "A"&&
AgeData[Age]<=EARLIER(AgeData[Age])
)
)
var y = CALCULATE(SUM(AgeData[Count]),FILTER(ALLSELECTED(AgeData),AgeData[Category]="A"))
return
DIVIDE(x,y)``````

``````B % =
var x = CALCULATE(
SUM('AgeData'[Count]),
FILTER(
'AgeData',
AgeData[Category] = "B"&&
AgeData[Age]<=EARLIER(AgeData[Age])
)
)
var y = CALCULATE(SUM(AgeData[Count]),FILTER(ALLSELECTED(AgeData),AgeData[Category]="B"))
return
DIVIDE(x,y)``````

``````C % =
var x = CALCULATE(
SUM('AgeData'[Count]),
FILTER(
'AgeData',
AgeData[Category] = "C"&&
AgeData[Age]<=EARLIER(AgeData[Age])
)
)
var y = CALCULATE(SUM(AgeData[Count]),FILTER(ALLSELECTED(AgeData),AgeData[Category]="C"))
return
DIVIDE(x,y)`````` 3. Then we can create four measures to get A, B, C and All.

``A = CALCULATE(MIN(AgeData[Age]),FILTER(AgeData,AgeData[A %]>=MAX(PercentilesToDisplay[Percentile])))``

``B = CALCULATE(MIN(AgeData[Age]),FILTER(AgeData,AgeData[B %]>=MAX(PercentilesToDisplay[Percentile])))``

``C = CALCULATE(MIN(AgeData[Age]),FILTER(AgeData,AgeData[C %]>=MAX(PercentilesToDisplay[Percentile])))``

``All = CALCULATE(MIN(AgeData[Age]),FILTER(AgeData,AgeData[%]>=MAX(PercentilesToDisplay[Percentile])))``

Put them to values and not add category to column, the result like this, If you have any question, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

Announcements #### June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news. #### Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 Top Solution Authors
Top Kudoed Authors
Users online (1,740)