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 ag...

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.

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.
- cabadart
  Microsoft Employee
  6 years ago
  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
  - v-zhenbw-msft
    Community Support
    6 years ago
    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.