Top N Filter and ALL

I have created a table visual and used the top n visual filter to give me the top N nominal business unit sales. Then I want to calculate the total sales for all the business unit.

My formula is:
Total Sales=sum(sales)

Grand Total Sales= CALCULATE([Total Sales], ALL('Account'[Nominal Business Unit])) //to remove the Top N visual filter on Nominal Business Unit

The number should be 1914 and it has been correct. I adjusted from Top 17 to Top 15, it’ correct. Then Top 14 doesn’t make sense anymore, because the grand total became 1910 when the Commercial filtered out by Top N visual filter. Then I adjust to top 10 and top 5, it stays at 1910.

I think the formula is correct but why when Commercial is filtered out by Top N visual filter, it is not calculating correctly. Do you guys know why and how to fix it?


Is there any other filter that is impacting the calculation? So, things like date or the likes? Otherwise, I think it should work fine. Specifically, given that it works with some of the TopN and not with the others.

Firstly I do not recommend using this Top N Filter at all. You should always write DAX in my opinion.

Everything can be done in DAX and has far more flexibility and extension possibilities when done in DAX.

I would recommend using TOPN here instead.

See here for more details on how to use TOPN.