Calculate amount of answers given to questions per year for all question columns in the table

[mfriedrich228]

Hi everyone,

I'm still fairly new to Power BI and learning a lot still, right now I have the following table structure of a survey in Power BI:

Question 1	Question 2	Question 3	Team	Year	Votecount
Yes	Maybe	Yes	Team 1	2019	1
No	No	No	Team 1	2020	1
Yes	No	Maybe	Team 1	2021	1
Maybe	No	No	Team 1	2020	1
Maybe	Yes	Yes	Team 1	2019	1
No	Yes	Maybe	Team 1	2021	1

So what I would like to do is to count the votecount for every answer per year per question, so I can display the calculated values in a bar chart later. For each year and answer there would be a bar. What I did try until now, I looked up different DAX formulas but none seemed really helpful for what I do here to my knowledge. What I got to work is the desired result for a single question, the formula I used is this one:

Calculated Votecount Question 1 = 
var _votes = sum('Question 1'[Votecount])
var _allVotes =
calculate(
    sum('Question 1'[Votecount]),
    ALLEXCEPT('Question 1','Question 1'[Year])
)

return divide( _votes, _allVotes)

So my question to you is, is what I'm trying to do there possible at all or is the approach really to do that for every question with a measure again?

Kind regards

Maximilian

[ValtteriN · Accepted solution]

Hi,

If you have acces to powerquery I would change the table a bit:

Select these columns and unpivot the values:

By doing this your table will look like this:

Now that the data is in this format we don't even need dax:

If you want to do this with DAX ping me with @

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[mfriedrich228]

It did solve my problem and I don't need to do it with DAX here, all I needed at this point was a solution to the problem, thanks a lot for your time and efforts!