Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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)
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
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?
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?
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |