Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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
@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-La...
Appreciate your Kudos.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |