Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
SamyHakim
Regular Visitor

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.

 

SamyHakim_2-1697618824830.png

 

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. 

SamyHakim_0-1697618725997.png

 

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.

 

SamyHakim_1-1697618786375.png

 

Thanks!

2 REPLIES 2
Anonymous
Not applicable

Hi @SamyHakim ,

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:

vcgaomsft_0-1697773696198.png

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

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:

SamyHakim_0-1697790307753.png

SamyHakim_1-1697790332742.png

 

PowerBI:

 

SamyHakim_2-1697790358884.pngSamyHakim_3-1697790372689.png

 

Not sure what could be the difference.

 

Thanks.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors