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!
Solved! Go to Solution.
Hi @cabadart ,
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.
Hi @cabadart ,
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.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
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.
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!
Hi @cabadart ,
We need to create a calculate table, then create a measure to meet your requirement.
1. Create a calculate table.
Table =
ADDCOLUMNS (
ADDCOLUMNS (
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.
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:
Hi @cabadart ,
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.
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
Hi @cabadart ,
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.
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.
Hi @cabadart ,
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.
Featured community members, changes to the Community, and more! Read up on recent Power BI community news.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications
User | Count |
---|---|
350 | |
202 | |
103 | |
65 | |
62 |
User | Count |
---|---|
378 | |
262 | |
139 | |
81 | |
65 |