Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JAGTSIS
Frequent Visitor

Dynamic percentage change of avg score based on group value

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 🙂 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @JAGTSIS ,

 

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.

 

queyr_dax.png

 
 
 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
JAGTSIS
Frequent Visitor

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
Super User
Super User

Hi @JAGTSIS ,

 

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.

 

queyr_dax.png

 
 
 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.