How to show no data in bar chart that is being referenced from a dropdown menu in sharepoint

[Anonymous]

Hello, I am currently creating a monthly status report dashboard and having trouble trying to showcase data that is directly being pulled from Sharepoint into Power BI. Within Sharepoint, there is a column that has a drop down menu showcasing these options: 

Currently we have 21 items that have the same status as "Initial Draft Complete" and it is showing up in the bar chart here:

I am trying to show the status' that are 0 for the remaining 7 status' but am having trouble having those points be returned into the bar chart as "0." I've even gone ahead and checked the "show items with no data" option within Power BI as well and still no luck:

I'm kind of stumped right now and need some guidance. How can I show the remaining values in the bar chart as 0?

[Anonymous]

Hi @Anonymous ,

I did not reproduce your problem in my testing.

Below are my test steps.

In the test, a Sharepoint list was used as the data source with the following status column.

In this list, 21 items have "Initial Draft Complete" status and the remaining 7 have no status.
Placing the Status field into the Y and X axes of the bar chart results in the following.

Best Regards,
Dengliang Li

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

[Anonymous]

Thanks for responding Dengliang! I may have caused confusion with my explanation and I apologize for that. The total number of items overall is 21 and currently they all hold the "Initial Draft Complete" status. I wanted to show that for the remaining status options (Overview Scheduled, Overview Conducted, Awaiting Evidence, etc.) that they would show up as 0 on the bar chart. So essentially I would have the 21 initial draft complete items, and 0 would be populated for the rest of the options in the chart. Hope that cleared up the confusion. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please refers to the below steps.

Create a single-column calculation table that stores all the choices for the /Status/ column.

DimStatus = {
    "Initial Draft Complete",
    "Overview Scheduled",
    "Overview Conducted",
    "Awaiting Evidence",
    "Delayed",
    "Internal Follow-up",
    "IRS Follow-up",
    "Approved"
}

In the bar chart, place the status column on the X-axis and the column of the calculation table on the Y-axis, and select /Show items with no data/.
The final result is as follows, hopefully it will your needs.

Best Regards,
Dengliang Li

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