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   