Forum Discussion
Solved
Summarize and Rank by multiple columns and rows
Total Revenue Client Name Year Month Revenue Stream -1625.7 ABC 2016 10 1 -11693.9 DEF 2016 10 2 -598.79 GHI 2016 10 3 -523.52 JKL 2016 10 1 -84.85 MNO 2016 ...
You may use SUMMARIZE Function to add a calculated table and then use RANKX Function to add a calculated column. The post below is for your reference.
create a summarize table, go to modelling tab, click new table and add following:
TableSummarize = SUMMARIZECOLUMNS(Table1[Client Name], Table1[Revenue Stream], "Total Revenue", SUM(Table1[Total Revenue]) )
Now in above table, add new calculated field for rank:
Rank = RANKX(Filter(TableSummarize, TableSummarize[Revenue Stream] = EARLIER(TableSummarize[Revenue Stream])),TableSummarize[Total Revenue],,DESC,Dense)
parry2k
Super User
Super Usercreate a summarize table, go to modelling tab, click new table and add following:
TableSummarize = SUMMARIZECOLUMNS(Table1[Client Name], Table1[Revenue Stream], "Total Revenue", SUM(Table1[Total Revenue]) )
Now in above table, add new calculated field for rank:
Rank = RANKX(Filter(TableSummarize, TableSummarize[Revenue Stream] = EARLIER(TableSummarize[Revenue Stream])),TableSummarize[Total Revenue],,DESC,Dense)
Hi parry2k- Thank you kindly the summarize table worked, however for my Rank all i get is a #1 for each- im trying to get top 50 clients within each stream by revenue, do i use asc,Skip instead?
- parry2k
can you share your rank formula, i ranked based on revenue stream, not sure if that is what you were looking for.