Calculate the Difference Between two Grouped Averages?

0
votes

Working in Power BI and I am trying to find the average number for two different groups and then calculate the difference between them. Here is an example of the data:

[Screenshot of data]

ID      Item        Misc        Score.I
R.3     Searching   N/A         0
R.3     Discovering N/A         0
R.3     Creation    Email       3.1
R.3     Duplicate   Mailbox     1.9
R.3     Duplicate   Connection  2.8
R.3_FS  Searching   N/A         0
R.3_FS  Discovering N/A         0
R.3_FS  Creation    Email       2.8
R.3_FS  Duplicate   Mailbox     2.8
R.3_FS  Duplicate   Connection  2.5

For example, I want to find the average Score.i for each ID and then show the difference between the two values. Any idea on the easiest way to accomplish this?

1
powerbiaveragedaxpowerbi-desktopcalculation
And what if the ID values aren't 2, but more (or less)? – Andrey Nikolov

1 Answers

1
votes

You could create 3 measures - average for ID = R.3, average for ID = R.3_FS, and difference between first two:

Average R.3 = AVERAGEX(FILTER('Table'; 'Table'[ID] = "R.3"); 'Table'[Score.I])

Average R.3_FS = AVERAGEX(FILTER('Table'; 'Table'[ID] = "R.3_FS"); 'Table'[Score.I])

Difference = [Average R.3] - [Average R.3_FS]

Where Table is the name of your table. Note, that you may have to use , instead of ; as parameters separation, depending on your regional settings.

enter image description here