Top N and other by grand total with monthly matrix table

[thomsontam]

Hi All,

I followed the following links and created Top 5 + Others for my report table.

https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/

https://community.powerbi.com/t5/Desktop/Top-10-Other/m-p/51282

https://community.powerbi.com/t5/Desktop/Dynamic-Top-N-and-Others-category/m-p/614566#M292949

It works great on grand total level, however, if I put the formula into a monthly matrix table then it is unable to provide correct information.

e.g.

In grand total table, I got the following:

Company	Total
AAA	50
BBB	45
CCC	40
Other	65
Total	200

However, when I transform it into a monthly matrix table, it shows as below

Company	P1	P2	P3	Total
AAA	20	15	20	50
BBB	15	14	16	45
CCC	14		14	40
DDD		13
Other	21	18	20	65
Total	70	60	70	200

instead of

Company	P1	P2	P3	Total
AAA	20	15	20	50
BBB	15	14	16	45
CCC	14	12	14	40
Other	21	19	20	65
Total	70	60	70	200

Thanks for your help!

[amitchandak]

@thomsontam , Can share your topN measure?

I think because of context topN will work for each period and give Top 5 for those. You might have force allexcept (Table, Table[Company])

[thomsontam]

Rank =IF (AND (NOT ( ISBLANK ( [Top & Other] ) ),ISFILTERED ( SalesData[Company] )),RANKX ( ALL(SalesData[Company] ), [Sales Total] ))

Sales Top = CALCULATE ([Sales Total],KEEPFILTERS (TOPN ( [TopN Selection], ALL ( SalesData[Company] ), [Sales Total] )))

Sales Other = CALCULATE ([Sales Total], KEEPFILTERS (EXCEPT (ALL ( SalesData[Company]),TOPN ( [TopN Selection], ALL ( SalesData[Company] ), [Sales Total] ))))

Sales Other Total Only =
IF ( NOT ( ISFILTERED ( SalesData[Company] ) ), [Sales Other] )

Sales Top & Other =
IF (
HASONEVALUE ( 'Top & Other'[Top & Other] ),
SWITCH (
VALUES ( 'Top & Other'[Top & Other] ),
"Top", [Sales Top],
"Other", [Sales Other Total Only]
),
[Sales Total]
)

Sales Total =
SUM ( SalesData[Sales Amount] )