Dynamic percentage change of avg score based on group value

[Anonymous]

Hi,

I haven't found a solution yet, so here I am! I am currently working with one survey that asks the same respondents on two different moments (i.e. 2 measurements) to their wellbeing score and contains some demographic information as well. See the table for a simplified version:

ID	Age	Gender	Measurement	Score A	Score B	Score C
1	30-40	M	1	2.5	5	3.5
2	40-50	F	1	3.5	2.5	4
3	20-30	F	1	4	4.5	3
1	30-40	M	2	3	5	3.5
2	40-50	F	2	4	4	3.5
3	20-30	F	2	3	4.5	3.5

What I'm looking for is a scorecard that shows (either the percentage or absolute) difference for one of the scores (e.g. Score A) between the 1st and 2nd measurement (i.e. Measurement column, 1 and 2). 

One solution I found is adding another column (e.g. 'Score A avg') in the query editor which calculated the average for the two measurements, thus every row containing 1 in Measurement column shows that same average, and every row containing 2 shows the average Score A for measurement 2. Unfortunately, this is kind of 'static' solution (for lack of a better word) as I have a few slicers in my dashboard that should apply on this % difference as well. What I mean by that: if the user selects 'M' in the Gender slicer, it should only show the % difference between measurement 1 and 2 for each individual with M in the Gender column.

My searches so far primarily come up with such static solutions (e.g. % change over time) while it really needs to be a dynamic calculation that updates with every change made in slicers. I think it therefore should be a measure in the dashboard view instead of adding a column. I tried in Quick measures the 'Average per category' calculation, with Score A as base value and Measurement as category. If I could this both for Measurement 1 and Measurement 2, then I could go again to Quick measures and use 'Percentage difference'. I'm not sure that will work and haven't figured out yet how to do it anyway.

Any help is very welcome 🙂 

[Anonymous]

Yes, this is great MFelix, thanks! At first, I wasn't sure as it gives percentage/absolute differences from M1 and M2 for all scores together. However, adding a filter based on attribute into a card, allows for just showing the difference for one of the scores. All other slicers work good as well.

If I wanted to do this for each score seperately, I could just repeat the steps (so to have 3 'Attribute' columns) for each of the scores?

[MFelix · Accepted solution]

Hi @Anonymous ,

Believe you have two options to treat your data one in DAX and another one in Query Editor.

Query Editor:

• Select all the columns that scores
• Unpivot those columns
• Select the column Measurement
• Pivot by Value
• Load your data
• Create the following measures:

Absolute = AVERAGE(UNPIVOT[1]) - AVERAGE(UNPIVOT[2])

% = DIVIDE( [Absolute]; AVERAGE(UNPIVOT[1]))

1 and 2 are the name of the columns for each time.

DAX - The first steps are to avoid making 4 measures for each of your Scores (A, B, C)

• In Query Editor 
  • Select all the columns that scores
  • Unpivot those columns
  • Load your data
• Create the following measures:

Average 1 = CALCULATE(AVERAGE(DAXSOLUTION[Value]);DAXSOLUTION[Measurement] = 1)

Average 2 = CALCULATE(AVERAGE(DAXSOLUTION[Value]);DAXSOLUTION[Measurement] = 2)

Absolute DAX = [Average 1] - [Average 2]

% DAX = DIVIDE( [Absolute DAX]; [Average 1])

Now just setup the visualizations has you need. In the example and attach I have made both options and sinc the slicers in order for you to see that the result is the same in both options, you just need to choose what is best for your analysis.