The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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!
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:
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:
PowerBI:
Not sure what could be the difference.
Thanks.