Forum Discussion

Stefan_38's avatar
Stefan_38
Frequent Visitor
3 years ago

Issues calculating Weighted Average

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 b...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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:

  • Stefan_38's avatar
    Stefan_38
    Frequent Visitor
    3 years ago

    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