Issues calculating Weighted Average

Question

Hi all,&nbsp;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.&nbsp;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.&nbsp;A simulation of a department with multiple resources is given in the picture below. Again the operation % should not be included in the allocation.&nbsp;&nbsp;&nbsp;So we get 86% instead of 97%.&nbsp;&nbsp;Measure =&nbsp;VAR SelectedDepartments =&nbsp; &nbsp; VALUES('Dim_Department'[Cost Centers])&nbsp;RETURNSUMX(&nbsp; &nbsp; SelectedDepartments,&nbsp; &nbsp; // Calculate for each department individually&nbsp; &nbsp; VAR CurrentDepartment = 'Dim_Department'[Cost Centers]&nbsp; &nbsp; VAR Weight_values =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_PowerApps_Outlook/Budget',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_PowerApps_Outlook/Budget'[LocalValue]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_PowerApps_Outlook/Budget'[Project ID] &lt;&gt; "99999",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_PowerApps_Outlook/Budget'[Project ID] &lt;&gt; "99998",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_PowerApps_Outlook/Budget'[Project ID] &lt;&gt; "99997",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_Department'[Cost Centers] = CurrentDepartment&nbsp; &nbsp; &nbsp; &nbsp; ) / 100&nbsp;&nbsp; &nbsp; VAR total_weight =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM('Dim_PowerApps_Outlook/Budget'[LocalValue]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_Department'[Cost Centers] = CurrentDepartment&nbsp; &nbsp; &nbsp; &nbsp; ) / 100&nbsp;&nbsp; &nbsp; VAR Weight_Average =&nbsp; &nbsp; &nbsp; &nbsp; DIVIDE(Weight_values, total_weight)&nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; Weight_Average&nbsp;&nbsp;I hope someone can help me crack this code&nbsp;Thank a ton in advance 🙂&nbsp;

greg_deckler · Answer

Stefan_38&nbsp;First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742eThis 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/63376Also, 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/547907Also: https://youtu.be/uXRriTN0cfYAnd: https://youtu.be/n4TYhF2ARe8
&nbsp;
This might also help:

stefan_38 · Answer

Hi Greg,&nbsp;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?&nbsp;Measure = VAR SelectedDepartments =&nbsp; &nbsp; VALUES('Dim_Department'[Cost Centers])RETURNSUMX(&nbsp; &nbsp; SelectedDepartments,&nbsp; &nbsp; // Calculate for each department individually&nbsp; &nbsp; VAR CurrentDepartment = 'Dim_Department'[Cost Centers]&nbsp; &nbsp; VAR Weight_values =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_PowerApps_Outlook/Budget',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_PowerApps_Outlook/Budget'[LocalValue]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_PowerApps_Outlook/Budget'[Project ID] &lt;&gt; "99999",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_PowerApps_Outlook/Budget'[Project ID] &lt;&gt; "99998",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_PowerApps_Outlook/Budget'[Project ID] &lt;&gt; "99997",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_Department'[Cost Centers] = CurrentDepartment&nbsp; &nbsp; &nbsp; &nbsp; ) / 100&nbsp; &nbsp; VAR total_weight =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM('Dim_PowerApps_Outlook/Budget'[LocalValue]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dim_Department'[Cost Centers] = CurrentDepartment&nbsp; &nbsp; &nbsp; &nbsp; ) / 100&nbsp; &nbsp; VAR Weight_Average =&nbsp; &nbsp; &nbsp; &nbsp; DIVIDE(Weight_values, total_weight)&nbsp; &nbsp; VAR Total_Working =&nbsp; &nbsp; &nbsp; &nbsp; Weight_Average * CALCULATE([Actual/Outlook], ALL('Dim_Project'))&nbsp; &nbsp; VAR Row_Wise_Working =&nbsp; &nbsp; &nbsp; &nbsp; Weight_values * CALCULATE([Actual/Outlook], ALL('Dim_Project'))&nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; //Weight_Average&nbsp; &nbsp; &nbsp; &nbsp;if(HASONEFILTER(Dim_Project[Project Group]),Row_Wise_Working,Total_Working)&nbsp; &nbsp; &nbsp; &nbsp; //Row_Wise_Working)&nbsp;Thank you in advance

Forum Discussion

Issues calculating Weighted Average

2 Replies

Related content

Calculating Weighted Average

Calculating Weighted Sales

Weighted Average DAX

calculating weighted average

weighted average calculation