Forum Discussion

cabadart's avatar
cabadart
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's avatar
    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's avatar
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.

cabadart's avatar
cabadart
Microsoft Employee
6 years ago

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

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    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.