cancel
Showing results for
Did you mean:
Frequent Visitor

## How to Rank from multi category and Sum by TOP N Rank

HI all;

i have a dataset with dimdate relationship, i'd like to Rank my total revenue based on group0, year and month, i add Year and Month Column in dataset, because ranked should be in one table ( please correct me if i wrong),

i have wrong result, with my formula;

my question are;

1. how to ranked to get as my expected,

2. is it possible to ranked with different table ( e.q Year and Month from dimdate, Category and measure from dataset table

3. how to summ total revenue, from Top N Ranked ( should i used parameter,new column or table )

here is the link for my pbix

thank you for your help and share;

warm regards;

vick

1 ACCEPTED SOLUTION
Frequent Visitor

Hi All;

My issue was solved, now everything looks fine;

here is the formula to solved this

Rank Revenue By Month = RANKX(FILTER(ALL('RankByMonth'[Group0Desc],'RankByMonth'[Month]),'RankByMonth'[Group0Desc]=MAX('RankByMonth'[Group0Desc])),CALCULATE([Revenue Ttl]),,DESC,Dense)

and the sum for TOP N

SUM TOP N = CALCULATE('RankByMonth'[Revenue Ttl],TOPN(SELECTEDVALUE('TOP Rank'[TOP Rank]),GROUPBY('RankByMonth',RankByMonth[Month]),CALCULATE([Revenue Ttl])))

here is the result

cheers;

vick

Frequent Visitor

Hi All;

My issue was solved, now everything looks fine;

here is the formula to solved this

Rank Revenue By Month = RANKX(FILTER(ALL('RankByMonth'[Group0Desc],'RankByMonth'[Month]),'RankByMonth'[Group0Desc]=MAX('RankByMonth'[Group0Desc])),CALCULATE([Revenue Ttl]),,DESC,Dense)

and the sum for TOP N

SUM TOP N = CALCULATE('RankByMonth'[Revenue Ttl],TOPN(SELECTEDVALUE('TOP Rank'[TOP Rank]),GROUPBY('RankByMonth',RankByMonth[Month]),CALCULATE([Revenue Ttl])))

here is the result

cheers;

vick

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.