Forum Discussion
Helper IINeed help with total sum
- You may have to use IN VALUES(Store[Store])
Essentially, you're wanting to clear the filter on Store, but only for the selected stores, so define
Impact =
VAR _SelectedStores = VALUES(Store[Store])
RETURN CALCULATE( SUM(Sales[Sales]), ALL(Store[Store]), Store[Store] IN _SelectedStores )
Something like that should hopefully work. AllisonKennedy Thanks again for the idea.
I came up with a workaround solution. I added another Item table to use as slicer instead of using the original one and forced relationship between 2 tables from 1:1 to 1:Many with single direction. So, the slice would filter the original table.
I updated my measure to get all items from slicer table and use it to filter original item table.AllStoreVol_2020 = VAR Items = VALUES('Item (2)'[Item]) RETURN CALCULATE(SUM(Sales[Amount]), 'Calendar'[Year] = 2020, 'ID'[ID] = 1, ALL('Item'), 'Item'[Item] IN Items)
Helper IIThank you for a suggestion. I was able to use below measure to display the correct total sales from each store in a matrix table.
Impact = CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Item[Item]), ALL(Store[Store]), Store[Store] IN Stores)
However, when I used a drilldown option, which my client preferred, I'm unable to get the total sale from each store. Any idea?
Community ChampionCan you share your sample file via OneDrive link please so I can do a few tests?
- Maxemus2000
AllisonKennedy Thanks again for the idea.
I came up with a workaround solution. I added another Item table to use as slicer instead of using the original one and forced relationship between 2 tables from 1:1 to 1:Many with single direction. So, the slice would filter the original table.
I updated my measure to get all items from slicer table and use it to filter original item table.AllStoreVol_2020 = VAR Items = VALUES('Item (2)'[Item]) RETURN CALCULATE(SUM(Sales[Amount]), 'Calendar'[Year] = 2020, 'ID'[ID] = 1, ALL('Item'), 'Item'[Item] IN Items) - Maxemus2000