Can you display 0% value on a stacked column chart?

[pminnov]

Hi, 

I have a data table containing rows of different projects and several columns including one that contains the following values: yes, no, don't know. I have used a stacked column chart to display the results of a measure that calculates the percentage of projects that indicated 'yes' on the y-axis while using years (e.g., 2023, 2022) for the x-axis. I've also ensured that a calculation of 0/countrows will occur when there are no 'yes' values so that 0% is the result. However, the issue I'm having is that, in the case where data slicer criteria results in 0%, the column chart is blank and doesn't indicate that there is a value of 0% in the chart area the way it would be displayed in a line chart. Is there a workaround for this? 

[Anonymous · Accepted solution]

Hi @pminnov ,

According to your statement, I think your the columns in your slicer should be from the related tables. So Power BI will only return the data and X axis which in filter range. This means the X axis with values return 0% has beem removed, so you couldn't find the result.

I suggest you to create two unrelated dimtable for slicer, like DimFundType and DimProvince.

Percentage =
VAR _SELECT1 =
    SELECTEDVALUE ( DimFundType[Fund Type] )
VAR _SELECT2 =
    SELECTEDVALUE ( DimProvince[Province] )
RETURN
    CALCULATE (
        [Percentage],
        FILTER (
            'TableName',
            'TableName'[Fund Type] = _SELECT1
                && 'TableName'[Province] = _SELECT2
        )
    ) + 0

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vicky_]

You can add +0 to the measure somewhere to force it to show a data point when it is 0. 

[pminnov]

I was able to ensure that 0% would result for blank values. The issue is that the column chart will not show a 0% value. Instead the chart appears blank and I'm trying to find a way to show the 0% in the chart.

[Anonymous · Accepted solution]

Hi @pminnov ,

According to your statement, I think your the columns in your slicer should be from the related tables. So Power BI will only return the data and X axis which in filter range. This means the X axis with values return 0% has beem removed, so you couldn't find the result.

I suggest you to create two unrelated dimtable for slicer, like DimFundType and DimProvince.

Percentage =
VAR _SELECT1 =
    SELECTEDVALUE ( DimFundType[Fund Type] )
VAR _SELECT2 =
    SELECTEDVALUE ( DimProvince[Province] )
RETURN
    CALCULATE (
        [Percentage],
        FILTER (
            'TableName',
            'TableName'[Fund Type] = _SELECT1
                && 'TableName'[Province] = _SELECT2
        )
    ) + 0

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pminnov · Accepted solution]

I solved the issue. I used an if(and(isblank) formula to calculate 0% in cases where there were no instances of the numerator value being counted ("Yes") in the data column [@22Newjobcreation] - which would be counted as blanks - and placed the data labels outside of the bars in the chart so the '0%' data label would show. The formula I used was: 

% of New Jobs Created = if(and(isblank('Data Table'[# of projects reporting new jobs created]),COUNT('Data Table'[ProjectNumber])>0), calculate(0/COUNT('Data Table'[ProjectNumber])),calculate(countrows('Data Table'),'Data Table'[@22Newjobcreation]="Yes"))/COUNT('Data Table'[ProjectNumber])

This was the result in cases where no 'yes' values occurred but there were data rows: