DAX Calculation

[vinaydavid]

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!

[v-deddai1-msft]

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

[amitchandak]

@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.