Include count of blanks in a chart

[Anonymous]

I have a table with a column called States, where values are things like Closed, In Process, Hold, etc. In some rows, the value in this column is blank. I am trying to create a pie chart to show the total number of each state, but it doesn't include the blank values. I know you can create a measure using COUNTBLANK to get the number of blanks, but how do I include this value as a piece of my pie chart?

The chart looks like this

But when I add my measure as a value it becomes like this.

I just want one pie chart where it shows each of the states, including blank.

[PaulDBrown]

@Anonymous 

I'm hazarding a guess here but maybe you can achieve this by:

Create a measure to count the blanks. Let's call this [count blanks measure].
now create a new measure which is:

final measure = [your current measure] + [count blanks measure]

[Anonymous]

My other values aren't calculated based on measures though. Are you saying I need to create a measure counting the number of each possible state and then combine it into a chart? I can see why this might work but I find it hard to believe there isn't any other way than to be creating measures for every possible value.

[PaulDBrown]

@Anonymous 

Sorry!! I did say I was hazarding a guess (while on the move..)

Ok, so, where do you want to show the blank values? as another field in the legend?

[Anonymous]

No problem. I want to show them as a slice of the pie.

[PaulDBrown · Accepted solution]

@Anonymous 

Actually here's a little surprise...

My sample data table:

The measures:

1) Count State = COUNT('DataTable'[State])

2) Count Blanks = COUNTBLANK('DataTable'[State])

3) The final measure to use in the visual:

Count Including Blanks =  [Count State] + [Count Blanks]

And this is what you get:

I guess it wasn't too bad a guess after all!

[Anonymous]

It worked, thanks so much- and I apologize for doubting you!

[PaulDBrown]

@Anonymous 

There is actually a more "elegant" measure which will work:

Alternative measure =
COUNTROWS ( SELECTCOLUMNS ( 'DataTable', "state", 'DataTable'[State] ) )

[VijayP]

@Anonymous 

In the Pie Chart Fields add Location into the Legend to get required visual!

[BA_Pete]

Hi @Anonymous ,

If you have access to Power Query (not direct query/live connection) then you can select your [State] column, go to Transform tab and Replace Values, then replace null with "Blank" or "".

This should then show the blanks as distinct dimension values on any visuals.

Pete

[Anonymous]

Unfortunately I can't edit the data since this is a power bi dataset.

[Pragati11]

Hi @Anonymous ,

First question. Are there BLANK values in the STATE column? I fthere are blanks then the piechart will show it by itself once you move the count in the values section. See below:

See I have got a LOCATION column with BLANK values and it shows in the pie-chart.

Can you share your screenshot as well like what you are moving at your end?

Thanks,

Pragati

[Anonymous]

Yes, the blanks are in the state column. Here is what the fields look like.

blankState is my measure to count the blanks, since they are not showing in the first chart.

[Pragati11]

Hi @Anonymous ,

Can you just move normal COUNT of STATE in the VALUES section? your STATE column already has BLANK values in it, so just moving it to LEGEND should show a blank. You don't need a different measure for counting blanks like you see in my screesnhots. I don't have a measure to count blanks independently.

Thanks,

Pragati

[Anonymous]

State in the legend and the Count of State are the same column, so it's technically already in the legend.

[Pragati11]

Hi @Anonymous ,

No they are not.

In the Legend section when you move STATE column, it shows unique values in your STATE column.

In the Values section when you move COUNT(STATE) column, it counts the number of rows for the unique values in STATE column.

Also, what values you get when you just move STATE column in  a table visual?

Do you see a BLANK value in this column? Because I am still not convinced on this part. If the STATE column has BLANK value then it should show it in the LEGEND on pie-chart and so does it's distribution.

See like I have at my end:

Thanks,

Pragati

[Anonymous]

When I moved Count of State into the legend, it just became State, like what I already had in there. There is a blank value in my State table.

Interestingly, when I have no fields in Values, the legend shows blank, but as soon as I add a field to values, it disappears. I have confirmed in the data that there are blanks (4 of them, to be exact).