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...
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

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
      Community 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