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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ar-data
Helper II
Helper II

Create a rank column based on summary

I have a simple data as followss:

 

YearCustomerAmount
Y1C1100
Y1C270
Y1C2150
Y2C1210
Y2C280
Y2C3120

 

Basically, a table which has Year wise data where a customer may have more than one line item with same or different amount value.

 

1. Now I want to create a rank column and raking should be based on total amount value for a customer for a year
2. And another rank column and ranking should be based on total amount value for a customer ignoring year or any other column

I was able to do this with a simple measure as follows:

1. Rank = 

RANKX(ALL(Info[Customer]), Sum(Info[Amount]))

However, I am not able to do the same using a column. the reason I need to create a column is to sort the legend in the stacked bar chart using rank column.
 
Thank you.
1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @ar-data ,

 

You can refer to the following calculated column:

>>1. Now I want to create a rank column and raking should be based on total amount value for a customer for a year

 

RankbyYear = var a = SUMMARIZE('Table','Table'[Year],'Table'[Customer],"_amount",SUM('Table'[Amount])) var b = ADDCOLUMNS(a,"_rank",RANKX(FILTER(a,'Table'[Year] =EARLIER('Table'[Year])),[_amount])) return SUMX(FILTER(b,'Table'[Year] = EARLIER('Table'[Year])&&'Table'[Customer] = EARLIER('Table'[Customer])),[_rank])

 

>>2. And another rank column and ranking should be based on total amount value for a customer ignoring year or any other column

 

Rank = var a = SUMMARIZE('Table','Table'[Customer],"_amount",SUM('Table'[Amount])) var b = ADDCOLUMNS(a,"_rank",RANKX(a,[_amount])) return SUMX(FILTER(b,'Table'[Customer] = EARLIER('Table'[Customer])),[_rank])

 

Capture.PNG

 

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

 

Best Regards,

Dedmon Dai

 

View solution in original post

7 REPLIES 7
v-deddai1-msft
Community Support
Community Support

Hi @ar-data ,

 

You can refer to the following calculated column:

>>1. Now I want to create a rank column and raking should be based on total amount value for a customer for a year

 

RankbyYear = var a = SUMMARIZE('Table','Table'[Year],'Table'[Customer],"_amount",SUM('Table'[Amount])) var b = ADDCOLUMNS(a,"_rank",RANKX(FILTER(a,'Table'[Year] =EARLIER('Table'[Year])),[_amount])) return SUMX(FILTER(b,'Table'[Year] = EARLIER('Table'[Year])&&'Table'[Customer] = EARLIER('Table'[Customer])),[_rank])

 

>>2. And another rank column and ranking should be based on total amount value for a customer ignoring year or any other column

 

Rank = var a = SUMMARIZE('Table','Table'[Customer],"_amount",SUM('Table'[Amount])) var b = ADDCOLUMNS(a,"_rank",RANKX(a,[_amount])) return SUMX(FILTER(b,'Table'[Customer] = EARLIER('Table'[Customer])),[_rank])

 

Capture.PNG

 

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

 

Best Regards,

Dedmon Dai

 

@v-deddai1-msft Thank you and it works as expected. If its ok can you please explain the "retrun Sumx(...)" part. I am not able to understand how that is adding a new column to the existing table from the values in summarized table (var a).

Hi @ar-data ,

 

We need to use aggregation function to get the value by filter function(maxx can also be used in the case).  Usually, we use the below formula to get data :

 

CALCULATE(SUM(TABLE[VALUE]),FILTER(Table,........));

 

But in summary table (temporary table )with self-created columns , if we use formula like above. We'll not get the self-created columns because the summary table is coming backward. We can use the sumx or maxx to put summary table forward to get the self-created columns.

 

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

 

Best Regards,

Dedmon Dai

ar-data
Helper II
Helper II

@sayaliredij @amitchandak Thank you for the response. 

 

The ranking has to be assigned after aggregating the Amount values for a particular Customer as there are more than one line item for each customer in each year. So I need something that iterates and then assigns rank. I tried 'Earlier' but somehow that's not working.

@ar-data , In case In missed something on earlier , refer sub category rank Under this topics 


https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns



Also check measure Rank
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

sayaliredij
Super User
Super User

@ar-data  - 

 

you can use the following formula (almost similar to you did ) to create a calculated column

Rank =
RANKX(
'Table',
'Table'[Amount]
)
 
It is generated as below
sayaliredij_0-1616587662645.png

 

and then you can use this rank column for sorting





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@ar-data , if need a column, you need to create a column rank like

 

Example
RANKX(Filter(Info,[Year] =earlier[Year]), Info[Amount])

 

 

Measure rank like, But you can not sort column on that

RANKX(Filter(allselected(Info[Year],Info[Customer]),[Year] =Max[Year]), calculate(Sum(Info[Amount])))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors