Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MandoPavs008
Helper I
Helper I

Sum of Rank 10 Categories only

I have a table with categories and percentage for which i have calculated rank. I wanted to know what will be the sum of categories till Rank 10 only and i need to divide each percentage by sum of Rank10. Can anyone help?

 

CatRankPercentSum of Rank 10Normalized Percentage (CAT PERCENT/SUM OF RANK10)
A111.76%11.76%18.05% 
B210.60%10.60%16.27%
C37.11%7.11%10.91%
D46.24%6.24%9.58%
E55.95%5.95%9.13%
F65.66%5.66%8.69%
G74.93%4.93%7.57%
H84.79%4.79%7.35%
I94.35%4.35%6.68%
J103.77%3.77%5.79%
K113.39%  
L123.03%  
M134.31%  
N142.88%  
O158.38%  
Total 87.15%65.16%100%
1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi , @MandoPavs008 

Thanks for your quick response and sorry for the delay response due to the two days holiday.

According to your description, the "rankx" and the "Percentage" are all measures in your side.

Here are the steps you can refer to :

(1)This is my test data:

vyueyunzhmsft_0-1674435831314.png

(2)I create two measures like you to test:

Rankx = RANKX( ALL('Table'[Cat]) , CALCULATE( SUM('Table'[Count])))

Percentage = DIVIDE( SUM('Table'[Count]) , CALCULATE( SUM('Table'[Count]) ,ALL('Table')))

 

(3)Then we can create two measures:

 

Sum of Rank 10 = var _t =ADDCOLUMNS( ALLSELECTED('Table'[Cat]) , "rankx" , [Rankx],"Percentage",[Percentage])

var _cur_cat = VALUES('Table'[Cat])

var _t2 = FILTER( _t ,[rankx]<=10 && [Cat] in _cur_cat)

return

SUMX(_t2 , [Percentage])

 

Normalized Percentage (CAT PERCENT/SUM OF RANK10) = var _total_percentage= CALCULATE( [Sum of Rank 10] , ALLSELECTED('Table'[Cat]))

return

[Sum of Rank 10] / _total_percentage

 

(4)Then we can put the measures on the visual and we can meet your need , the result is as follows:

 

vyueyunzhmsft_1-1674435872709.png

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

5 REPLIES 5
v-yueyunzh-msft
Community Support
Community Support

Hi , @MandoPavs008 

Thanks for your quick response and sorry for the delay response due to the two days holiday.

According to your description, the "rankx" and the "Percentage" are all measures in your side.

Here are the steps you can refer to :

(1)This is my test data:

vyueyunzhmsft_0-1674435831314.png

(2)I create two measures like you to test:

Rankx = RANKX( ALL('Table'[Cat]) , CALCULATE( SUM('Table'[Count])))

Percentage = DIVIDE( SUM('Table'[Count]) , CALCULATE( SUM('Table'[Count]) ,ALL('Table')))

 

(3)Then we can create two measures:

 

Sum of Rank 10 = var _t =ADDCOLUMNS( ALLSELECTED('Table'[Cat]) , "rankx" , [Rankx],"Percentage",[Percentage])

var _cur_cat = VALUES('Table'[Cat])

var _t2 = FILTER( _t ,[rankx]<=10 && [Cat] in _cur_cat)

return

SUMX(_t2 , [Percentage])

 

Normalized Percentage (CAT PERCENT/SUM OF RANK10) = var _total_percentage= CALCULATE( [Sum of Rank 10] , ALLSELECTED('Table'[Cat]))

return

[Sum of Rank 10] / _total_percentage

 

(4)Then we can put the measures on the visual and we can meet your need , the result is as follows:

 

vyueyunzhmsft_1-1674435872709.png

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

v-yueyunzh-msft
Community Support
Community Support

Hi , @MandoPavs008

Based on your detailed requirement description and expected output data result provided, we can learn that you wanted to know what will be the sum of categories till Rank 10 only and you need to divide each percentage by sum of Rank10, I've made some local test and I figured a method that you can achieve this using two measures creating, here's my method, you can try:

 

Sum of Rank 10 =

var _value=IF(MAX('Table'[Rank])<=10,SUM('Table'[Percent]),BLANK())

var _total=SUMX(FILTER(ALLSELECTED('Table'),'Table'[Rank]<=10),'Table'[Percent])

return

IF(HASONEVALUE('Table'[Cat]),_value,_total)

 

Normalized Percentage (CAT PERCENT/SUM OF RANK10) =

var _total=SUMX(FILTER(ALLSELECTED('Table'),'Table'[Rank]<=10),'Table'[Percent])

return

IF(HASONEVALUE('Table'[Cat]),IF(MAX('Table'[Rank])<=10,DIVIDE(SUM('Table'[Percent]),_total),BLANK()),1)

 

And you can create a table visual to achieve this requirement like this:

vyueyunzhmsft_0-1674181747212.png

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

I tried with the measures i have but i got 100% for all under rank 10 in normalized percentage. I am unable to get the 

18.05% 
16.27%
10.91%
9.58%
9.13%
8.69%
7.57%
7.35%
6.68%
5.79%

Thank you for the solution. Just wanted to update my question that rank and percenatge are measures and the actual table has only CAT and COUNT from which i have calculated measures for rank and percentage. So can you help me with how do i calculate sum of categories till Rank 10 only and divide percentage by sum of Rank10. I am kind of confused with using measures to calculate sum of categories till rank 10

jaweher899
Super User
Super User

You can use the SUMX and FILTER functions in DAX to calculate the sum of the categories for the top 10 ranks, and then divide each percentage by that sum. Here is an example of how you might write the DAX code for this calculation:

SumRank10Categories:= SUMX(FILTER(Table,Table[Rank]<=10),Table[Category])

This measure will return the sum of the categories for the top 10 ranks.

To divide each percentage by the sum of top 10 categories, you can create another measure and divide it by the above measure.

DivideBySumRank10:=Table[Percentage]/[SumRank10Categories]

This measure will divide the percentage of each row by the sum of the top 10 categories.

You can then add this measure to your visual to see the result.

You can also use the measure in a calculated column to get the value for each row of your table.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.