cancel
Showing results 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

Helper II

## Create a rank column based on summary

I have a simple data as followss:

 Year Customer Amount Y1 C1 100 Y1 C2 70 Y1 C2 150 Y2 C1 210 Y2 C2 80 Y2 C3 120

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
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])

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

7 REPLIES 7
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])

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

Helper II

@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).

Community Support

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

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.

Super User

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

Also check measure Rank

Super User

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

and then you can use this rank column for sorting

Proud to be a 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])))