Sales Percentages by Category

[Anonymous]

Hi Guys,

I'm trying to calculate the percentage of sales that come from a packaged discount across a number of states. I've created a dashboard with slicers on this packaged discount, state, and sales person. I want to calculate the percentage of total sales for a selected package, while also applying slicers for state and salesperson. Based on the research, using the SUMX and ALL function seemed like the best way of doing this. My code is as follows:

Selected Category Sales Percentage = SUMX('Query1', Query1[TOTAL_SALES])/CALCULATE( SUM(Query1[TOTAL_SALES]), ALL(Query1[PACKAGED DISCOUNT])) 

What I need is for my denominator to calculate total sales among ALL packages while still applying slicers based on State and Salesperson. My numerator should simply apply all filters, therefore showing me the percentage of sales coming from that selected package across that selected state. Seems like it should be easy enough, however my denominator is not staying constant, even when I ONLY change the packaged discount slicer. Does anyone know why the above code does not work in this manner? Or perhaps someone could suggest a better way of doing this? My case is very similar to the second and third examples in the following Microsoft Document:

https://docs.microsoft.com/en-us/dax/all-function-dax#example

Any and all help is greatly appreciated!

[Anonymous]

Since a regular SUM expression applies all filters, I'm focusing more on how to disregard the one slicer in the denominator. Based off what I've read online, the following expression should work:

Total Sales Selected = calculate(sum(Query1[TOTAL_SALES]), ALL(Query1[C_PACKAGED_DISC]))

However I'm still coming up with innacurate numbers that change when I select different packages in the slicer. Because the total sales across all packages is a constant, the denominator, therefore, should remain the same regardless of the package I slice by.

[Anonymous]

Have you tried adding ALLSelected for state and sales person in the denominator?

[Anonymous]

---

@Anonymous wrote:
Have you tried adding ALLSelected for state and sales person in the denominator?

---

This doesn't work. I'm still getting the filtered total, which is the same as using a sum on the Total Sales column.

What I need is to sum total sales on all packages, regardless of which package I choose in the adjacent slicer. My resulting visual should then display the percentage of sales in that selected package.

Is there a function out there that disregards a specific slicer/filter on a given column?