Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
New to BI aand can't figure out how to do this. There are 2 tables. Table 1 has Product Group, Product Sub Group and Item. Table 2 has Country, Years, Sales and Item. The Matrix has two Slicers, 1 for Country and 1 for Product Group. How do you calculate the percentage of Total Sales for each Product Sub Group for each Year? I want the Matrix to look like below. Any help would be appreciated.
Solved! Go to Solution.
Hi @hdsogno ,
The formula that parry2k suggests works like a charm. And for your situation, you also could try using the function of ALLSELECTED like this:
Percentage_ALLSELECTED =
DIVIDE (
SUM ( 'Table'[Sales] ),
CALCULATE ( SUM ( 'Table'[Sales] ), ALLSELECTED ( 'Table' ) )
)
You could watch the video to learn about the difference between the two functions.
The Main Difference Between ALL And ALLSELECTED - Power BI & DAX
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have managed to calculate the percentage for each month for each year (Divide). I would like your support to replicate the same but for the categories per year, I cannot make the sums by category (A, B) for each year and thus calculate their corresponding percentage per year.
I have managed to calculate the percentage for each month for each year (Divide). I would like your support to replicate the same thing but for the categories per year, I cannot make the sums per category (A,B) for each year and in this way calculate its corresponding percentage per year.
taje by month for each year, I want to replicate it by category for each year.
Hi @hdsogno ,
The formula that parry2k suggests works like a charm. And for your situation, you also could try using the function of ALLSELECTED like this:
Percentage_ALLSELECTED =
DIVIDE (
SUM ( 'Table'[Sales] ),
CALCULATE ( SUM ( 'Table'[Sales] ), ALLSELECTED ( 'Table' ) )
)
You could watch the video to learn about the difference between the two functions.
The Main Difference Between ALL And ALLSELECTED - Power BI & DAX
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Xue Ding -
You and parry2K made my day! His solution gave me the percentage of all products. I was was looking for a percentage of only the products selected so I changed the ALL in his solution to the ALLSELECTED that you suggested and that did the trick.
Thanks again!
@hdsogno here it is
Measure =
DIVIDE(
SUM ( 'Table'[Amount] ),
CALCULATE( SUM ( 'Table'[Amount] ), ALL('Table'[Product Sub Group]))
)
Would appreciate Kudos 🙂 if my solution helped.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks for your help. Your solution works to get the percentage of ALL products. I was looking for the percentage of only the products selected. Substituted ALLSELECTED instead of ALL in your solution and it did the trick. Thanks again.
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |