Forum Discussion
Helper IINeed help with total sum
Hi Community,
My "sample" project has 3 tables; Sales, Item, and Store. Sales is a fact table and the other two are dimension. Both Item and Store have 1:* relationship with Sales table.
Impact is a measure which calculate a sum of of sales table.
Impact = CALCULATE(SUM(Sales[Amount])
Below is a result when I put it in a matrix with Store and Item. You see that Store A has a total sale of 7,250, B is 2,250, and C is 1,200.
Here is a sinario. My client wants to see a percentage of each item sales at each store, but instead of drilling down by store, my client wants to "drill up" with a matrix or bar chart (drill down from item to store). They want to see a percentage sale of the item total sale of each store when expand the item in a matrix or drill down on a chart. So, I modify my mesure to use all to capture all sales from each store and it works. Below is showing a total sale by store drillign down from item.
Impact = CALCULATE(SUM(Sales[Amount]), ALL('Item'[Item]))
Now, the issue that i'm tryign to solve. If I select a couple items from a slicer, the total store sales (without ALL statement) should be Store A 3,000 and store B 0. (drill down from store).
But, when I drilled down from item with a measure (with ALL statement), my total sale store doesn't reflect that. it still shows all total.
I've tried "ALLSELECTED" and it wouldnt give me the total I wanted.
Impact = CALCULATE(SUM(Sales[Amount]), ALLSELECTED('Item'[Item]))
How do I get a total sale calculation to shows up as intended with a filter (Store A 3,000 and B 0) so when I calculate sale percantage it doesn't show 0%? Please help, Thanks in advance.
- 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)
8 Replies
- AllisonKennedy
Community Champion
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.
Maxemus2000Thank 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?
- AllisonKennedyDrilldown 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?