Forum Discussion
DAX Calculation
Hi All,
I am planning to plot the below data into a stacked bar graph.
With Week on the X-axis (values like Curr.wk, forecast, +1 wk.....+3wk).
And on Y-axis I want to plot Type (XX, YY, ZZ .....etc.,)
| Type | Category | Week | Value | Capacity |
| XX | AA | +3 Week | 28/08/2020 | 5 |
| XX | AA | +3 Week | 29/08/2020 | 5 |
| XX | AA | +3 Week | 25/08/2020 | 5 |
| XX | AA | +3 Week | 26/08/2020 | 5 |
| XX | AA | +3 Week | 26/08/2020 | 5 |
| XX | AA | +3 Week | 28/08/2020 | 5 |
| XX | BB | +3 Week | 28/08/2020 | 3 |
| XX | CC | +3 Week | 25/08/2020 | 2 |
The below is what I want to achieve using a DAX measure.
| AA | =6 (count of 'Value')/5 (Avg. of 'Capacity') | =1.20 |
| BB | =1/3 | =0.33 |
| CC | =1/2 | =0.50 |
| =2.03 | ||
| XX | =2.03 |
So that, I can show for week '+3 Week' as 2.03 for Type (Legend).
Thanks in advance!
2 Replies
vinaydavid The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format?For Week vs Week data refer
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Appreciate your Kudos.Hello @vinaydavid ,
Please try the following measurement:
Measure = IF ( HASONEVALUE ( 'Table'[Category] ), CALCULATE ( DIVIDE ( COUNTROWS ( 'Table' ), AVERAGE ( 'Table'[Capacity] ) ), ALLEXCEPT ( 'Table', 'Table'[Category] ) ), SUMX ( SUMMARIZE ( 'Table', 'Table'[Category], "_a", CALCULATE ( DIVIDE ( COUNTROWS ( 'Table' ), AVERAGE ( 'Table'[Capacity] ) ), ALLEXCEPT ( 'Table', 'Table'[Category] ) ) ), [_a] ) )If this post helps, then consider Accepting it as the solution to help other members find it more quickly.
Best regards
Dedmon Dai
