Forum Discussion

benwebber92's avatar
benwebber92
Frequent Visitor
9 years ago
Solved

Calculating the Average Weekly % Difference between Expected and Actual figures

I have the below table in Excel imported into Power BI as a query which shows the week number of a group of people's Predicted and Actual figures for a task. 

While I can easily create a matrix that shows the Total Predicted, Total Actual, Total Difference and % Difference for the figures for each week, I also need to be able to calculate the Average Weekly % Difference - in other words, the arithmetic mean all of the '% Difference' figures.

 

Does anybody know how this could be done in Power BI?

 

edit: to confirm, the second table in the post is actually a representation of the Matrix I've created in Power BI rather than a table created in Excel. 

 

WeekPersonPredictedActual
1A81474
1B11629
1C48385
1D4871
1 169252
1F48844
1G372268
1H58294
1I188244
1J48221
2K487372
2L391140
2M305262
2N387304
2O9287
2P30490
2Q10330
2R130103
3S342406
3T494307
3U168110
3V209386
3W236424
3X473365
4Y493460
4Z301366
4AA118389
4AB251474
4AC51228
4AD35668
4AE16827
4AF205251

 

WeekPredictedActualDifference% Difference
124851782-703-28.3%
221061688-418-19.8%
319221998764.0%
41943226332016.5%
Grand Total84567731-725-8.6%

 

Average weekly % Diff-6.9%
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi benwebber92,

     

    You can create a measure like below. For details, you can see the attached Average weekly % Diff.pbix file.

     

    Average weekly % Diff =
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE (
                'Table1',
                'Table1'[Week],
                "Predicted", SUM ( Table1[Predicted] ),
                "Actual", SUM ( Table1[Actual] ),
                "Diff", SUM ( Table1[Actual] ) - SUM ( Table1[Predicted] )
            ),
            "%Diff", [Diff] / [Predicted]
        ),
        [%Diff]
    )

     

     

     

    Best Regards,
    QiuyunYu

3 Replies

  • ausrine's avatar
    ausrine
    Frequent Visitor
    9 years ago

    Wouldn't it be enough to create a measure with AVERAGE(%Diff)?

    • benwebber92's avatar
      benwebber92
      Frequent Visitor
      9 years ago

      Sorry to confirm, the second table in the post is actually a representation of the Matrix I've created in Power BI rather than a table created in Excel.

       

      Difference and % Difference are both measures that I created in Power BI.

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Icon for Community Support rankCommunity Support
        9 years ago

        Hi benwebber92,

         

        You can create a measure like below. For details, you can see the attached Average weekly % Diff.pbix file.

         

        Average weekly % Diff =
        AVERAGEX (
            ADDCOLUMNS (
                SUMMARIZE (
                    'Table1',
                    'Table1'[Week],
                    "Predicted", SUM ( Table1[Predicted] ),
                    "Actual", SUM ( Table1[Actual] ),
                    "Diff", SUM ( Table1[Actual] ) - SUM ( Table1[Predicted] )
                ),
                "%Diff", [Diff] / [Predicted]
            ),
            [%Diff]
        )

         

         

         

        Best Regards,
        QiuyunYu