Forum Discussion
Hide column in chart instead of filtering?
I have a chart in Power BI that summarizes responses to multiple agreement statements. (If someone selected "Strongly agree" or "Somewhat agree" in the survey to one of the statements, that column % increases.) However, I am unable to hide the column which has the percent who did not agree without changing the percentages in the other columns. I can absolutely filter out "No", but that will show incorrect results.
How do I simply hide an option in a chart without filtering the rest of the data based on it?
The reason your percentages change when you exclude "No" is that the visual's implicit percentage is calculated over the currently visible categories, so removing "No" also removes it from the denominator. The fix is to write an explicit measure whose denominator ignores the axis filter, then hide "No" at the visual level.
Assuming your responses live in a table like Responses[Answer], and the axis of the chart is Responses[Answer]:
% of Total = DIVIDE( CALCULATE ( COUNTROWS ( Responses ) ), CALCULATE ( COUNTROWS ( Responses ), ALL ( Responses[Answer] ) ) )Use this measure as the value in the chart, then open the visual level filter for Responses[Answer] and uncheck "No". Because ALL(Responses[Answer]) releases the filter on Answer only for the denominator, the totals stay stable and Strongly Agree, Somewhat Agree etc. keep showing their true share of all respondents.
If Answer sits on a dimension table instead of the fact table, use ALL on the dimension column and the same approach still works.
If this helped, please give it a kudos and mark it as the accepted solution.
Best,
Shai Karmani
2 Replies
- Shai_Karmani
Super User
The reason your percentages change when you exclude "No" is that the visual's implicit percentage is calculated over the currently visible categories, so removing "No" also removes it from the denominator. The fix is to write an explicit measure whose denominator ignores the axis filter, then hide "No" at the visual level.
Assuming your responses live in a table like Responses[Answer], and the axis of the chart is Responses[Answer]:
% of Total = DIVIDE( CALCULATE ( COUNTROWS ( Responses ) ), CALCULATE ( COUNTROWS ( Responses ), ALL ( Responses[Answer] ) ) )Use this measure as the value in the chart, then open the visual level filter for Responses[Answer] and uncheck "No". Because ALL(Responses[Answer]) releases the filter on Answer only for the denominator, the totals stay stable and Strongly Agree, Somewhat Agree etc. keep showing their true share of all respondents.
If Answer sits on a dimension table instead of the fact table, use ALL on the dimension column and the same approach still works.
If this helped, please give it a kudos and mark it as the accepted solution.
Best,
Shai KarmaniThanks! It works now that I created new measures.
But I want Power BI to develop a "hide" button in addition to their current "filter" buttons. This is a workaround for something that should be very simple.
