CALCULATE SUM ALL does not ignore slicers

[ShNBl84]

I am trying to do some predictive measures for a restaurant supplier company.

The bottom graph shows the number of plates we have ever sold and the % of total sales by day of week. I edited the interactions for this visualization to ignore all slicers.

The top graph has the same measures but without the interactions being edited to ignore each slicer on the report page. 

The 'PlatesSoldAll' calculation is CALCULATE(SUM(PlateSales[PlatesSold]),ALL(PlateSales)). The numbers change with every slicer I use.

'Plate' is simply a SUM function that is working fine. The data of this column is only measuring last week's sales specifically (which data stops at Wednesday for this example).

I would like all the columns to be in the same table so I can do some prediction/analysis.

My goal is to be able to look at the UNFILTERED plate sales (like on bottom) and compare them to THIS WEEK'S sales (Plate column) and predict how many we will need for the remaining days for the week.

For example, if we sold just over 3M plates Mon-Wed (which is usually 28% of our weekly business) how many can we expect to sell this Thursday (usually 10.51% of our weekly business) or Saturday (22.32% of our weekly business)?

I need to be able to use slicers to filter by week and location, but I also need the total numbers (like on bottom) unfiltered as a constant to compare and calculate against.

As mentioned above, the 'PlatesSoldAll' calculation is CALCULATE(SUM(PlateSales[PlatesSold]),ALL(PlateSales)), but is still filtering based on my slicers.

Help would be much appreciated! Thanks!

[edhans]

Take a look at my file. ALL() works correctly. I have not disabled any slicer interactions. The total sales in my fake data is 30,391, and I can filter by month (separate table) or product (sales table) and the Grand Total Sales measure always works.

Grand Total Sales = 
CALCULATE(
    [Total Sales],
    ALL(SalesTable)
)

If you cannot figure it out, please post a sample file.

[ShNBl84]

@edhans

Thank you for the file.

Part of my question is how to get the plate sales to align by day of week (see the bottom graph). As shown in the upper graph, I can already get the plates to show the same total sales while ignoring the day of week.

I think part of the difficulty is that some of the filters I am using (such as our company's special date periods and the day of the week) are held in related tables and not in the sales data itself.

[edhans]

To ensure any filtering by any date data works correctly, you should:

1. have Date table that has all of the relevant columns you need, so obviously the date, but the week number, month number, quarter number, and any other combinations, like Month Name, Month Name SHort (3 letters), YYYYMM.
2. THere should be a one to many relationship from your date table to any of your FACT tables (sales, orders, etc).
3. Only use fields from the date table as the slicer or filter. In other words, don't use the date from the Sales table in your slicer, but the date from the Date table. Same with week number. Use that in your table, not the week number in your sales table.

Sometimes it makes little or no difference. Other times it is the difference between a measure working or not. For example, I learned the hard way that if I dropped the date field from my sales table in my Matrix view, my SAMEPERIODLASTYEAR() measure didn't work at all - kept showing blank values.

Because you are working with dates in your data, clean it up so all date and date related fields in your visuals are coming from your date table (and you've set it as the date table) and then see if that fixes it.

[ShNBl84]

@edhans

I am using a calendar like you have suggested. I can't get the data to sort in the day of week row unless I edit interactions and remove all interactions, in which case I will not be getting this week's data. 

I tried putting CALCULATE SUM and then used ALL for all 5 data tables that are involved in the visual. It succeeded in getting rid of all the filters, but had the same number for all rows (ignoring day of week).

[v-piga-msft]

Hi @ShNBl84,

If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best  Regards,

Cherry

[themistoklis]

@ShNBl84

You can go to edit interactions on PowerBI and select "none" (icon with red circle) so as to igore all slicers.

Image below: