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

[vicks]

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

https://drive.google.com/open?id=1pDcPIgYbM72M8QgYPnYBBGiTI0G3rQtE

thank you for your help and share;

warm regards;

vick

[vicks · Accepted solution]

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