Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Using SUMPRODUCT in PowerBI

Hi, I am trying to create a sumproduct measure in powerbi using the below logic on excel. I need it to calculate dispatch rates per week based on weighted average.

 

Below is a small example of the logic.

 

 

The DR column is how I am currently calculating it in powerbi by taking the sum of the actuals and dividing it by the sum of the target or the plan. 

 

What I am looking for is to change this measure to calculate it as per the last column in the above table which is based on sumproduct of every week. Like below.

 

 

Thanks!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Please create a new measure, like:

    Measure = 
VAR __numerator =
    SUMX (
        FILTER (
            SUMMARIZE (
                ALL ( 'Egypts Adherence' ),
                'Egypts Adherence'[Index],
                "_actual", [actual],
                "_dr", [dr]
            ),
            'Egypts Adherence'[Index] <= MAX ( 'Egypts Adherence'[Index] )
        ),
        [actual] * [dr]
    )
VAR __denominator = CALCULATE([actual],'Egypts Adherence'[Index]<=MAX('Egypts Adherence'[Index]))
VAR __result = DIVIDE(__numerator,__denominator)
RETURN
    __result

    And the output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

       

      Thanks for the reply, I think this is somewhat close to the solution but for some reason, it is still different than the outcome I am getting on excel.

       

      Excel:

       

      PowerBI:

       

       

      Not sure what could be the difference.

       

      Thanks.