Weighted average in DAX with wrong total

[NonoP]

Hello,

I am doing a double weighted average:

I have for every month, the associated number of days per month, one column category, the associated power of the category, the value I want to average. Here is an example of the table:

The thing is I want to weight the value according to the number of days in a month and the "Power" of the associated category.

To do that I have a quite simple measure, the table is named "Plant":

Av_Tot_Plant_TEST =
CALCULATE (
    SUMX ( Plant, Plant[Availability] * Plant[Nb_jours] * Plant[Max_Power (MW)] )
        / ( SUMX ( Plant, Plant[Nb_jours] * Plant[Max_Power (MW)] ) )
)

The result works well if I chek the results per category, or if I check as a total per month.

However I know the global total to be wrong. The global is the final value if I take into account all months and all categories.

I believe it is because one of the category is present only on a couple of months and thus the total power varies through the year.

How could I tweak the measure to get the correct Total?

Thanks in advance

[danextian]

Hi @NonoP 

If you want your measure to be evaluted for each month then summed up at the total level.

Av_Tot_Plant_TEST =
VAR _tbl = 
    ADDCOLUMNS (
        SUMMARIZE('datetable', 'datetable'[month]), -- Create a summarized table by month
        "@avg", 
        DIVIDE( -- Calculate the average availability
            SUMX(
                Plant,
                Plant[Availability] * Plant[Nb_jours] * Plant[Max_Power (MW)] -- Numerator: Weighted availability
            ),
            SUMX(
                Plant,
                Plant[Nb_jours] * Plant[Max_Power (MW)] -- Denominator: Total days multiplied by max power
            )
        )
    )
RETURN
    SUMX(_tbl, [@avg]) -- Sum the calculated averages for each month

[bhanu_gautam · Accepted solution]

@NonoP , Try using 

DAX
Av_Tot_Plant_TEST =
AVERAGEX (
VALUES ( Plant[Month] ),
CALCULATE (
SUMX ( Plant, Plant[Availability] * Plant[Nb_jours] * Plant[Max_Power (MW)] )
/ SUMX ( Plant, Plant[Nb_jours] * Plant[Max_Power (MW)] )
)
)

[NonoP]

Thanks you!!

[johnt75]

Try

Av_Tot_Plant_TEST =
SUMX (
    VALUES ( Plant[mois] ),
    CALCULATE (
        SUMX ( Plant, Plant[Availability] * Plant[Nb_jours] * Plant[Max_Power (MW)] )
            / ( SUMX ( Plant, Plant[Nb_jours] * Plant[Max_Power (MW)] ) )
    )
)