topic Re: Survey questions - change year on year in DAX Commands and Tips

Survey questions - change year on year

Fizzgig08 — Tue, 03 Jan 2023 19:00:47 GMT

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!

Re: Survey questions - change year on year

jewel_at — Tue, 03 Jan 2023 22:23:53 GMT

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

Re: Survey questions - change year on year

Fizzgig08 — Wed, 04 Jan 2023 07:56:42 GMT

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!

Re: Survey questions - change year on year

jewel_at — Wed, 04 Jan 2023 16:39:43 GMT

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

Re: Survey questions - change year on year

jewel_at — Thu, 05 Jan 2023 14:59:31 GMT

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