cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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.

image.png

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]))

 

image.png

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

 

image.png

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. 

image.png

I've tried "ALLSELECTED" and it wouldnt give me the total I wanted. 

 

Impact = CALCULATE(SUM(Sales[Amount]), ALLSELECTED('Item'[Item]))

 

image.png

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


If you found this post helpful, please give Kudos.


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


excelwithallison.com

View solution in original post

Highlighted

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

image.png

View solution in original post

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


If you found this post helpful, please give Kudos.


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


excelwithallison.com

View solution in original post

Highlighted

@AllisonKennedy ,

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)

 

 

image.png         image.png

However, when I used a drilldown option, which my client preferred, I'm unable to get the total sale from each store. Any idea?

image.pngimage.png

Highlighted

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. 


If you found this post helpful, please give Kudos.


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


excelwithallison.com

Highlighted

@AllisonKennedy 

 

Please see the link Sample File 

Highlighted

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

Did I answer your question? Mark my post as a solution!

Highlighted

@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

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

Highlighted

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

image.png

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors