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)
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.
Thank 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?
- Drilldown essentially changes the selected values, so alters what the ALLSELECTED total is calculating.
Can you share your sample file via OneDrive link please so I can do a few tests?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)
Hi Maxemus2000 ,
After checking your sample .pbix file,the data and total are showing correctly,could you pls provide more details about errors.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
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?
