Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Maxemus2000
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
AllisonKennedy
Super User
Super User

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

@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
AllisonKennedy
Super User
Super User

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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

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!

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

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?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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

@AllisonKennedy 

 

Please see the link Sample File 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.