Measure that calculates the % of answers per year while respecting site and global filter

[mfriedrich228]

Hi everyone, 

I have a table with the following structure:

Question	Answer	Year	Team	Count	SortingID
Are you happy with your job?	Yes	2021	Team Development	1	1
Are you happy with your job?	No	2020	Team HR	1	3
Are you happy with your job?	Not sure	2019	Team Marketing	1	2
Do you like your boss?	Yes	2021	Team PR	1	1
Do you like your boss?	Not sure	2020	Team HR	1	2
Do you like your boss?	No	2019	Team Development	1	3

Now I would like to create a Measure that calculates % for every answer per year while respecting that there is sitewide filter for the team and page filter for the question. At the end I want to display those results in a bar chart where the calculated value is the value, the legend is the year and the axis are the answers. So if a value exists I want to display bars per answer, one for every year. I tried to play around with measures a bit and also tried to modify one I got here as a helping answer but I couldn't get it properly to work. That's what I have right now:

Question % = Divide(
count( 'Top 10 Questions for all Teams'[Answer] ),
Calculate(
count( 'Top 10 Questions for all Teams'[Answer] ),
ALLEXCEPT('Top 10 Questions for all Teams','Top 10 Questions for all Teams'[Year])
)
)

I would be thankful for any help or hints what I might do wrong here or what I need to change in order to make it work properly.

KInd regards

Maximilian

[AlexisOlson]

Try adding ALLSELECTED to preserve the filter context from the report and page filters.

Question % =
DIVIDE (
    COUNT ( 'Top 10 Questions for all Teams'[Answer] ),
    CALCULATE (
        COUNT ( 'Top 10 Questions for all Teams'[Answer] ),
        ALLSELECTED (),
        ALLEXCEPT (
            'Top 10 Questions for all Teams',
            'Top 10 Questions for all Teams'[Year]
        )
    )
)

[mfriedrich228]

That calculates wrong results, maybe to make it easier for you to understand what I would like to get, I have a small calculation example:

Team HR:
2021: 10 Votes, 5 Yes, 2 Not sure, 3 No -> 50% Yes, 20% Not sure, 30% No
2020: 10 Votes, 3 Yes, 2 Not sure, 5 No -> 30% Yes, 20% Not sure, 50% No
2019: 10 Votes, 6 Yes, 4, Not sure -> 60% Yes, 40% Not sure, 0% No

I hope this illustrates my goal better than my original post, because I think the blaming is on my requirements and certainly not your DAX skills 🙂

Kind regards

Maximilian

[AlexisOlson]

With the data in your post, you could put Year on rows and Answer on columns to get this

Using this measure:

Question % = 
DIVIDE (
    COUNT ( 'Top 10 Questions for all Teams'[Answer] ),
    CALCULATE (
        COUNT ( 'Top 10 Questions for all Teams'[Answer] ),
        ALL ( 'Top 10 Questions for all Teams'[Answer] )
    )
)