topic Re: Issues calculating Weighted Average in DAX Commands and Tips

Issues calculating Weighted Average

Stefan_38 — Thu, 27 Jul 2023 19:19:31 GMT

Hi all,

I have been working on a weighted average calculating in order to allocated costs down to projects bested on time estimates, where we have a “project” called operations which should not be included in the costs

We consider departments and resource types. It works fine when only having one resource type selected and if I select more than one only the row by row context works, but the total doesn’t.

It also works with multiple departments but the measure happens with multiple resources. The relationship between the tables are one to many and seems to work fine.

I have discovered that the issues raises in the weighted score being calculated wrongly as it adds the percentages together instead of calculating the weighted score.

A simulation of a department with multiple resources is given in the picture below. Again the operation % should not be included in the allocation.

So we get 86% instead of 97%.

Measure =

VAR SelectedDepartments =

VALUES('Dim_Department'[Cost Centers])

RETURN

SUMX(

SelectedDepartments,

// Calculate for each department individually

VAR CurrentDepartment = 'Dim_Department'[Cost Centers]

VAR Weight_values =

CALCULATE(

SUMX(

'Dim_PowerApps_Outlook/Budget',

'Dim_PowerApps_Outlook/Budget'[LocalValue]

'Dim_PowerApps_Outlook/Budget'[Project ID] <> "99999",

'Dim_PowerApps_Outlook/Budget'[Project ID] <> "99998",

'Dim_PowerApps_Outlook/Budget'[Project ID] <> "99997",

'Dim_Department'[Cost Centers] = CurrentDepartment

) / 100

VAR total_weight =

CALCULATE(

SUM('Dim_PowerApps_Outlook/Budget'[LocalValue]),

'Dim_Department'[Cost Centers] = CurrentDepartment

) / 100

VAR Weight_Average =

DIVIDE(Weight_values, total_weight)

RETURN

Weight_Average

I hope someone can help me crack this code

Thank a ton in advance 🙂

Re: Issues calculating Weighted Average

Greg_Deckler — Thu, 27 Jul 2023 20:13:02 GMT

Stefan_38 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

This might also help:

Re: Issues calculating Weighted Average

Stefan_38 — Fri, 28 Jul 2023 07:06:41 GMT

Hi Greg,

Than you much for the reply. It help for sure and the measure looks clear now and the result follows. However, the total is still not calculating correctly as we can see below. Its still using the 86% but I think its because my measure below is correct in the total part as it doesnt sum. Do you have any suggestions for modifications to the last part to get it to fully work?

Measure =

VAR SelectedDepartments =

VALUES('Dim_Department'[Cost Centers])

RETURN

SUMX(

SelectedDepartments,

// Calculate for each department individually

VAR CurrentDepartment = 'Dim_Department'[Cost Centers]

VAR Weight_values =

CALCULATE(

SUMX(

'Dim_PowerApps_Outlook/Budget',

'Dim_PowerApps_Outlook/Budget'[LocalValue]

'Dim_PowerApps_Outlook/Budget'[Project ID] <> "99999",

'Dim_PowerApps_Outlook/Budget'[Project ID] <> "99998",

'Dim_PowerApps_Outlook/Budget'[Project ID] <> "99997",

'Dim_Department'[Cost Centers] = CurrentDepartment

) / 100

VAR total_weight =

CALCULATE(

SUM('Dim_PowerApps_Outlook/Budget'[LocalValue]),

'Dim_Department'[Cost Centers] = CurrentDepartment

) / 100

VAR Weight_Average =

DIVIDE(Weight_values, total_weight)

VAR Total_Working =

Weight_Average * CALCULATE([Actual/Outlook], ALL('Dim_Project'))

VAR Row_Wise_Working =

Weight_values * CALCULATE([Actual/Outlook], ALL('Dim_Project'))

RETURN

//Weight_Average

if(HASONEFILTER(Dim_Project[Project Group]),Row_Wise_Working,Total_Working)

//Row_Wise_Working

)

Thank you in advance