Survey questions - change year on year

[Fizzgig08]

Hi all,

I'm new to Power BI and seeking help/guidance.

I am creating a report using survey data. One of the visuals is a bar graph showing the score for each question in a particular survey section for 2022. My data also includes the score for the same questions in 2021 but I don't want this displayed in the visual so have filtered to only show data for 2022. I have set up my slicers, so I can filter by department and survey section to show the relevant questions and scores for the current year, and all works as expected.

I would now like to use conditional formatting to show whether the score for each question has increased or decreased since 2021 through the colour of the bar (red for decrease, green for increase for example).

I assume I will need to create a new measure to achieve this, but I'm not sure. Guidance and help appreciated!

[jewel_at]

Hi @Fizzgig08 

Do you mean that you have both 2021 and 2022 data in the same table? Like this? 

If yes, then you can try to create a new table with DAX 

var CurrentTotal =
SUMMARIZE(CALCULATETABLE('Table', 'Table'[Year] = "2022"), 'Table'[Question], "CurrentTotalScore", SUM('Table'[Score]))

var PreviousTotal =
SUMMARIZE(CALCULATETABLE('Table', 'Table'[Year] = "2021"), 'Table'[Question], "PreviousTotalScore", SUM('Table'[Score]))

RETURN NATURALLEFTOUTERJOIN(CurrentTotal, PreviousTotal)

Then create a new calculated measure

HasIncreased = 
if(SUM('Table 2'[CurrentTotalScore]) > sum('Table 2'[PreviousTotalScore]), 1, 0)

To apply the conditional formatting on your bar chart, go to the Visualization settings, and setup Rules on the conditional formatting

Since you have filters, you would have to add a relationship with the DAX table and original data table. Or just add those columns in the DAX table when you group it by, and change the filters. 

You can donwload the sample here:

Compare If Has Increased 

This might give you some idea. 

I hope this helps though!

Jewel

[Fizzgig08]

Yes that is what I mean! 

I will give it a go today and see how I get on!

One question - is your solution looking at the total score for 2021 vs the total score for 2022? I need it to look at question A, for example, and see whether question A's score improved or declined Year on Year, then the same for every question.

Thanks so much for your help!

[jewel_at]

Hi @Fizzgig08 

I found a better way to solve this, instead of having a calculated table where you have to manually enter the year, you can use  the following calculated measures. This way, you can use this report in future years.

CurrentScoreSum = 
CALCULATE(SUM('Table'[Score]))

PreviousYearSum = 
var currentSelectedYear = SELECTEDVALUE('Table'[Year])
return 
CALCULATE(
    SUM('Table'[Score]), 'Table'[Year] = CONVERT(currentSelectedYear -1, STRING))

ScoreHasIncreased = 
IF([CurrentScoreSum] > [PreviousYearSum], 1, 0)

You can download the sample .pbix here:

Compare Scores 

Hope this helps!

Jewel

[jewel_at]

Hi @Fizzgig08 ,

Yes it does! That is what this measure is doing, comparing if 2022's score has increased or improved compared to 2021

HasIncreased = 
if(SUM('Table 2'[CurrentTotalScore]) > sum('Table 2'[PreviousTotalScore]), 1, 0)

Hope you're on track with your report!

Jewel