Weighted average of 2 attributes with values in the same value-column

[Anonymous]

Hello,

my dataTable looks like this:

I want to calculate the weighted day-average of  Analyse.Gcv[kWh/m³] based on the hour-values of attribute ML.Energy[kWh].

It is possible with a calculated column, but is there a way to do this more efficient with a DAX formula? 

maybe withe AVERAGEX, buth I don't know how to itterate in the same column. 

Thanks in advance!

[yaman123]

Hey, how can this be done with more than 2 attributes such as attribute A, B, C, D, E etc

Thanks

[v-yulgu-msft]

Hi @Anonymous ,

Not very clear about your desired result, please illustrate "weighted average" with examples.

How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[Anonymous]

Hi, 

I give another example (with calculation) out of excel:

Date	Attribute	Value
1/jan	A	5
2/jan	A	0
3/jan	A	63
4/jan	A	2
5/jan	A	1
6/jan	A	44
1/jan	B	23
2/jan	B	25
3/jan	B	26
4/jan	B	20
5/jan	B	12
6/jan	B	20
AvG A	=AVERAGE(C2:C7)	19,1667
Weighted average A	=SUMPRODUCT(C2:C7;C8:C13)/SUM(C8:C13)	21,3095

How can I achieve the same calculation in Power BI without unpivot the attribute-column.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

Please refer to below measures:

Measure_1 =
CALCULATE (
    AVERAGE ( Test_8[Value] ),
    FILTER ( ALLSELECTED ( Test_8 ), Test_8[Attribute] = "A" )
)

Measure_2 =
VAR A_value =
    IF (
        SELECTEDVALUE ( Test_8[Attribute] ) = "B",
        CALCULATE (
            SUM ( Test_8[Value] ),
            FILTER (
                ALL ( Test_8 ),
                Test_8[Attribute] = "A"
                    && Test_8[Date] = MAX ( Test_8[Date] )
            )
        )
    )
VAR _product =
    SUM ( Test_8[Value] ) * A_value
RETURN
    _product

Measure_3 =  SUMX(ALLSELECTED(Test_8),[Measure_2])

Measure_4 =
[Measure_3]
    / CALCULATE (
        SUM ( Test_8[Value] ),
        FILTER ( ALLSELECTED ( Test_8 ), Test_8[Attribute] = "B" )
    )

Best regards,

Yuliana Gu