Help with visual calculation

[Fusilier]

Hi,

I've created a visual calculation measure which seems to do as I hoped:

Percent of grand total = DIVIDE([Sum of row count], COLLAPSEALL([Sum of row count],COLUMNS))*100

I'm using it in a clustered bar chart:

When I drill down to subsequent levels (the levels are Department/Category/Question - This is data from a survey),

It does work, showing the correct percentages at that level:

However, if I filter out any of the Aggregated responses (either Agree, Neutral, Disagree), it recalculates the remaining responses, so if I just want to see 'Agree' responses, I end up with 100% bars as it's recalulating just the remaining responses in the visual.

How would I adjust the measure so that it shows 'Agree' responses without recalculating. So, here for example, I want to see 73.98 for Culture, 72.24 for Engagement etc. etc.?

Hope somebody can help?

[lbendlin · Accepted solution]

Either use HASONEVALUE as a filter for your measure, or use separate visuals, one for Percent of Grand Total, and one for just Percent.

[Ashish_Mathur · Accepted solution]

Hi,

I think the ALL() function will also have to be used somewhere.  To receive further help, share the download link of the PBI file.  Show the problem there clearly.

[lbendlin · Accepted solution]

Either use HASONEVALUE as a filter for your measure, or use separate visuals, one for Percent of Grand Total, and one for just Percent.