Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sales Percentages by Category

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!

3 REPLIES 3
Anonymous
Not applicable

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
Not applicable

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


@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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.