Forum Discussion
Need 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)
v-kelly-msft
If you are looking at attached pictures in the thread, you will see that when I selected Item X and Y we get a total Store sale (A = 3000 , B = 0, and C = 2700). However, when I drill down on Item X, we get a total store sale of A = 500, B = 1250, and C = 1100. What I need is to have the total store sale stay at A= 3000, B = 0, and C = 2700 so I can calculate the item sale percentage for that store.
OwenAuger You were helping me with a similar issue before ... do you have any solution to this?