Forum Discussion

Wanghongyu's avatar
Wanghongyu
New Member
8 years ago
Solved

Need help to calculate weighted average

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"...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Wanghongyu
    New Member
    8 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Wanghongyu
        New Member
        8 years ago

        This is excatly what I want. Thanks so much