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.
Can anyone help with the appropriate DAX to solve this.
I have this data
project# | defect type | qty |
1 | a | 3 |
1 | b | 1 |
1 | c | 1 |
1 | a | 2 |
1 | a | 1 |
1 | a | 1 |
1 | b | 2 |
2 | a | 1 |
2 | b | 1 |
2 | b | 1 |
2 | b | 2 |
2 | c | 1 |
2 | a | 3 |
2 | c | 1 |
2 | a | 1 |
2 | c | 1 |
I need to create a stacked bar graph to show percent defect per category a,b,c per each project. I used Dax and it gives me on the stacked bar graph 100% all the time. I am unable to allow it to consider the denimator as the whole number of instances per project 1 or 2.
I did it in excel for simplification. I used pivot to count #defects per quantity by each defect type and divided over total sum of
a | 7 |
b | 3 |
c | 1 |
a | 5 |
b | 4 |
c | 3 |
this is what i want to graph (i can use stacked bar in pbi)
project 1 | project 2 | |
a | 64% | 42% |
b | 27% | 33% |
c | 9% | 25% |
I appreciate the help
Solved! Go to Solution.
@Fair-UL here is the measure
% = DIVIDE ( SUM ( 'Table'[qty] ), CALCULATE ( SUM ( 'Table'[qty] ), ALLEXCEPT ( 'Table', 'Table'[project#] ) ) )
here is the output
here is how you do it
I would 💖 Kudos 🙂 if my solution helped. If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
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.
Try like
divide(sum(Table[Qty]),calculate(sum(Table[Qty]),allexcept(Table[defect type])))
I used this same dax before but with Count and ALL, was not correct
now I used yours but I changed the ALLEXCEPT to ALL and it gave me the graph I need. Thank you so much @amitchandak
@Fair-UL here is the measure
% = DIVIDE ( SUM ( 'Table'[qty] ), CALCULATE ( SUM ( 'Table'[qty] ), ALLEXCEPT ( 'Table', 'Table'[project#] ) ) )
here is the output
here is how you do it
I would 💖 Kudos 🙂 if my solution helped. If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
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.
Thank you @parry2k I used the same DAX with ALL not ALLEXCEPT as I had multiple layer drilldown in the stacked bar chart and I used all those columns for unfiltering the denominator. Thanks again, the graphs are great!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |