Recently, I have been working on a Power BI report where I want to create a bar chart showing zero values. The problem is that the data set has no zero value; it has blank cells, which we need to show as zero.
Also, we can create a Power BI bar chart that doesn’t show zero values.
Show Blank Value as Zero in Power BI Bar Chart
For this example, I created a table (Product table) using the Enter data feature in Power BI.
The data table shows that the “mouse” and “mike” prices are null or blank.
If we use this data to create a bar chart in Power BI Desktop to display the price based on product, it will only show the data for products with available prices, as shown in the screenshot below.
But we want to show the blank Value as Zero, and if we add a data label, the data label shows as zero. We can achieve this using two ways:
- By using a measure.
- Using the Power Query editor to replace the null values with 0.
Display Zero Value in Power BI Bar Chart using Measure
Let me show you how to create a measure that will replace the null values with 0.
1. Open Power BI Desktop and create the above data table using the “Enter data” option. Then, under the Modeling tab, click “New measure.”
2. In the formula bar, put the below Dax expression.
Total Price = CALCULATE(SUM('Product'[Price])) +0
3. select the Stacked bar chart from the Build pane to create a bar chart. Then, drag and drop the Product on the Y-axis and the Total Price onto the X-axis.
4. Next, go to the Format pane and enable the Data label option. Then, you can see the zero value in the Power BI stacked bar chart.
Display Zero Value in Power BI Bar Chart using Power Query Editor
Like measure, let me show you how to use the Power Query editor to replace null values with zero.
After loading the data into Power BI Desktop, under the Home tab, click Transform data.
Then, the Power Query Editor will open. Select the Price column and click Replace value.
A Replace Values popup will appear. In it, put Value To Find as null and Replace With as 0, then click OK.
After that, the null value will be replaced with 0. Then, under the File tab, click Close & Apply.
You can then directly create the bar chart and enable the data labels to see the zero values.
These are the two ways you can show the blank Value as Zero in the Power BI bar chart.
Hide Zero Value in Power BI Bar Chart
Sometimes, we may need to hide the zero value in the Power BI bar chat. For example, in the above bar chart, you see the price for the “Mouse” and “Mike” products is 0. Now, I want to hide these two products in the bar chart.
We can achieve this using a Filters panel:
- Select the bar chat and expand the Filters pane. Under Filter on the visual section, expand the Sum of Price option.
- Then, Show items when the value is not 0. Then click on the Apply filter.
Now you can see the Power BI bar chart doesn’t show a 0 value.
This is how to hide zero value from the Power BI bar chart.
Show blank Value in Power BI Bar Chart
While researching how to Show Zero Values in a Power BI Bar Chart, I found another method. However, if we add a data label to the bar chart, it does not show zero, which means it shows a blank value.
For example, we have created a table (a Product table) using the Enter data feature in Power BI.
The above data shows that the price is blank for “Mouse” and “Mike.”
If we use this data to create a bar chart that will display the price based on product, it will only show the data for products with available prices, as shown below.
To display “Mouse” and “Mike” in the chart, we must enable the option ‘Show items with no data.’
To do this, select the Stacked bar chart from the Build pane to create a bar chart. Next, drag and drop the Product to the Y-axis and the Price to the X-axis.
To show the blank values in the Power BI bar chart, right-click on the Product and select the ‘Show items with no data‘ option.
Now you can see the Power BI bar chart showing the blank value in the Power BI bar chart.
Conclusion
Now, if your data set has a blank or null value, you can easily change it to zero, and if you want, you can show the blank value in the Power BI Bar chart.
In the above, I clearly explain how we can show a blank value in the Power BI bar chart and hide a zero value in the Power BI bar chart.
You may also like:
- Power BI Bar Chart Show Value and Percentage
- Power BI Bar Chart Rounded Corners
- Apply Conditional Formatting in the Power BI Bar Chart
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
