% of total using dynamic filters but unaffected by TopN filter

[laurencowie]

Hello! Hopefully somebody can help...

Is it possible to calculate a percentage total which changes dynamically based on other filters/slicers on the page, but is not affected by filtering the table to show the top 10 results?

For example, I have sales by date, business and category. For each of these I will have a slicer to allow a subset to be considered. I have calculated the %Total Sales, so that if I have all the data, its the percentage of all sales, but if I choose for example just one business, it´s the percentage of that business:

All data combined:

Date	Business	Category	Sales	Percent	Rank
01/07/2018	A	1	100	11%	4
01/07/2018	A	2	150	17%	2
01/06/2018	A	1	125	14%	3
01/07/2018	B	1	75	9%	6
01/07/2018	B	2	25	3%	7
01/07/2018	C	1	300	34%	1
01/07/2018	C	2	100	11%	4

Filtered for Business A:

Date	Business	Category	Sales	Percent	Rank
01/07/2018	A	1	100	27%	3
01/07/2018	A	2	150	40%	1
01/06/2018	A	1	125	33%	2

Now, I want to only show the top observation based on the variable percent. If I just say, filter for highest percentage, or for rank=1, it recalculates the percentage to be 100%. Can I change it to ignore only the top n filter, and still calculate the percentage based on all other active filters?

Many thanks in advance!

[Greg_Deckler]

Sounds kind of like an ALLEXCEPT where you exclude Rank. But, I would probably try CALCULATETABLE where you change the filter for Rank to ALL('Table'[Rank]) (column version of ALL) which might remove the TOPN filter if it is based on Rank.

[laurencowie]

Thanks Greg.

I think ALLEXCEPT would work if Rank was a column, but I need it to be a measure so that it changes with the filters.

I have had a look at the CALCULATETABLE function but I'm afraid I don't follow what you mean, or understand exactly how to use the function. Again it seems to not like the fact that rank is a measure. Is that correct or have I misunderstood?

[Greg_Deckler]

OK, if you post your Rank measure formula, I'll see if I can come up with a full solution.

[laurencowie]

Ok, so below is my measure for rank, and my measure for the percent_sales which is the measure I would like to change based on the other filters but not be affected by setting the table to show rank 1-10 only. (Equally, I could use the TopN filter on my BaseItems using top 10 based on Sales column if that is more straightforward, but that also doesn't seem to be working correctly with the ALLEXCEPT!)

Ranking= RANKX( ALL(report_excel[BaseItem]); calculate(SUM(report_excel[Sales] )))

percent_sales =

   VAR Sales =
       CALCULATE(SUM ( report_excel[Sales] ))
   VAR AllSales =
       CALCULATE( SUM ( report_excel[Sales] ); ALLselected('report_excel'))
   RETURN
       DIVIDE ( Sales; AllSales )

Thanks for your help!