Need help to calculate weighted average

[Wanghongyu]

I have a database similar to below left table. But I would like to summariz it to right hand side pivot.

I manage to get two measure in data model one is "sum of schedule" another one is "weightage".

sum of schedule:=SUM('table'[schedule])

weightage:=DIVIDE([sum of schedule],CALCULATE([sum of schedule],ALLSELECTED()))

I can do a multiplication to get all individual values as displayed in cells J4 to J6. However, I couldn't get

value J7 which is the sum of J4 to J6 by using DAX function. 

Appriciate if anyone could help.

[Anonymous]

HI @Wanghongyu,

Please share the sample pbix file to test, I'm not so sure where the machine column come from and relationship to current tables.

Regards,

Xiaoxin Sheng

[Wanghongyu]

Sorry. Machine is in column A and it's not captured before.

[Anonymous · Accepted solution]

Hi @Wanghongyu,

You can try to use below measure to achieve your requirement:

Current Schedule = 
VAR current_machine =
    LASTNONBLANK ( Sheet2[Machine], [Machine] )
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( Sheet2 ),
            [Machine],
            "Total Schedule", SUM ( Sheet2[Schedule] ),
            "Percent", SUM ( Sheet2[Schedule] ) / SUMX ( ALLSELECTED ( Sheet2 ), [Schedule] )
        ),
        "Current", [Total Schedule] * [Percent]
    )
RETURN
    IF (
        COUNTROWS ( Sheet2 ) <> COUNTROWS ( ALLSELECTED ( Sheet2 ) ),
        MAXX ( FILTER ( summary, [machine] = current_machine ), [Current] ),
        SUMX ( summary, [Current] )
    )

Regards,

Xiaoxin Sheng

[Wanghongyu]

This is excatly what I want. Thanks so much