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...
Need Help
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.