Top N Filter and ALL

DAX DAX Calculations
1

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?

Top17
Top17.PNG501×741 40.8 KB

Top%2015
Top 15.PNG506×600 32.8 KB

Top%2014
Top 14.PNG510×571 32.4 KB

Top%2010
Top%205

2

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.

3

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.