cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Need 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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
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.

______________

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

excelwithallison.com

Highlighted
Helper II

@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 8
Highlighted
Super User II
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.

______________

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

excelwithallison.com

Highlighted
Helper II

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?

Highlighted
Super User II
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?

______________

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

excelwithallison.com

Highlighted
Helper II

Highlighted
Community Support

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,
Kelly

Highlighted
Helper II

@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.

Highlighted
Helper II

@OwenAuger  You were helping me with a similar issue before ... do you have any solution to this?

Highlighted
Helper II

@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)``````

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors