cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

Display specific percentiles in a table

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

PercentileAge
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
Community Support

Re: Display specific percentiles in a table

Hi @cabadart ,

 

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

 

1. First delete the relationship between two tables.

 

Dis1.jpg

 

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)

 

Dis2.jpg

 

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,

 

Dis3.jpg

 

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.

View solution in original post

9 REPLIES 9
Highlighted
Community Support
Community Support

Re: Display specific percentiles in a table

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]))                                       

 

Display 1.jpg

 

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.

Highlighted
Microsoft
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:

PercentileMeasureExpected (true) value
50%2292
75%23712
90%23932
95%23948
99%239117
99.9%239209
99.99%239236

 

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
Community Support

Re: Display specific percentiles in a table

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] )
)

 

Display 1.jpg

 

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,

 

Display 2.jpg

 

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
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:
image.png

 

Highlighted
Community Support
Community Support

Re: Display specific percentiles in a table

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.

 

Dis 1.jpg

 

Column = 
var x = [Index]+1
var y = CALCULATE(SUM([Percentile]),FILTER('PercentilesToDisplay',PercentilesToDisplay[Index]=x))
return
IF(ISBLANK(y),1,y)

 

Dis 2.jpg

 

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,

 

Dis 3.jpg

 

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
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:

image.png

So your first method calculated the percentiles for All correctly, but not for any filtering

Highlighted
Community Support
Community Support

Re: Display specific percentiles in a table

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
)

 

DIS1.jpg

 

2. Then we can create a relationship between Age table and PercentilesToDisplay table.

 

DIS2.jpg

 

3. At last we can create a simple measure,

 

AgeAtXPtile = CALCULATE(MIN(AgeData[Age]))

 

DIS4.jpgDIS3.jpg

 

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
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
Community Support

Re: Display specific percentiles in a table

Hi @cabadart ,

 

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

 

1. First delete the relationship between two tables.

 

Dis1.jpg

 

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)

 

Dis2.jpg

 

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,

 

Dis3.jpg

 

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.

View solution in original post

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

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

Using the Community

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

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors