Power BI - Problem with calculating grand total for weighted average

[sr97]

Hi all, 

I'm trying to calculate a weighted average of delivery precision based on volume. Each ID (Column 1) in the table has a volume (Column 4) and a delivery precision ( Column 3). The weighted average is Column 3* Column 5 ( the weight of a volume for a given week)

If I sum column 6 in excel, I get a total of 0.9376 (correct answer). However, Power BI gives me a value of 160325 ( which doesn't make sense). I have tried to find a solution to this problem using both youtube and this forum, without any success.

This is my approach:

Weighted average_Delivery =

VAR _TotalVolume = CALCULATE(SUM('Data'[Volume]),ALL('Data’[ID])) // Calculating the total volume for a given week

VAR _Nominator = CALCULATE(SUM('Data -'[Volume])) // finding the volume for a given ID

VAR VolumeWeight =CALCULATE((_Nominator/_TotalVolume)) // Calculating the weight to find the weighted average

VAR DeliveryPrecision = CALCULATE(SUM('Data’[DeliveryPrec]),ALLEXCEPT(Calendar,Calendar[WeekNum],Calendar[Month],Calendar[Year])) // Delivery precision for an ID

VAR DeliveryprecisionTable = ADDCOLUMNS(SUMMARIZE('Data','Data ‘[ID],Calendar[WeekNum]), "DeliveryP", DeliveryPrecision, "Wt", VolumeWeight) // Creating a table with ID, weeknumber, Delivery precision and weight

RETURN SUMX(VALUES('Data ‘[ID]), CALCULATE(SUMX(DeliveryprecisionTable, [Wt] * [DeliveryP]))) // Sum to calculate the weighted average

[miTutorials · Accepted solution]

Try the below measure and let me know if it works!

WeightedAverage_Delivery =
VAR _TotalVolume = CALCULATE(SUM('Data'[Volume]), ALL('Data'[ID])) 

VAR DeliveryprecisionTable =
    ADDCOLUMNS(
        SUMMARIZE('Data', 'Data'[ID], Calendar[WeekNum]),
        "DeliveryP", CALCULATE(SUM('Data'[DeliveryPrec])),
        "Wt", DIVIDE(CALCULATE(SUM('Data'[Volume])), _TotalVolume)
    )

RETURN 
SUMX(DeliveryprecisionTable, [Wt] * [DeliveryP])