Forum Discussion

NonoP's avatar
NonoP
New Member
1 year ago
Solved

Weighted average in DAX with wrong total

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

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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)] )
    )
    )

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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)] )
    )
    )

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    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)] ) )
    )
)
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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