Forum Discussion

Microsoft Employee

6 years ago

Solved

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!

Need Help

v-zhenbw-msft

6 years ago

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.

9 Replies

v-zhenbw-msft
Community Support
6 years ago
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.
- cabadart
  Microsoft Employee
  6 years ago
  I created a sample data file https://microsoft-my.sharepoint-df.com/:u:/p/cabadart/EXNoRVG-Qu1JlIRN_WPDHOEBEA9OBO9eP8cwwFHhqgg6UA?e=1yDaR1
  
  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!
v-zhenbw-msft
Community Support
6 years ago
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.
- cabadart
  Microsoft Employee
  6 years ago
  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:
  - v-zhenbw-msft
    Community Support
    6 years ago
    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.