Include zero/blanks in column chart (frequency table)

[Anonymous]

I have a table with number of emails sent per case

Data sample

Case id	Number of sent emails
11111	3
22222	null
33333	2
44444	1
55555	1

Result:

Problem

As you can see am I missing a column for 0 E-mails sent (the column should be high with +50k in frequency). I have tried to replace the null data with zeroes without improvment. I can also not find any option "include blank data". How should i proceed?

[Tahreem24]

@Anonymous ,

RIght click on your axis in Field option under Visualization pane --> Select "Show item with no data". (Attched screen shot)

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 

[Anonymous]

It has a green check mark but the 0-bar is till empty.

[Tahreem24]

@Anonymous ,

You should be able to just add 0 to the measure this will return the sum of frequency (if there are some observations else it will return 0).

Sum of Frequency = Sum( 'fTable'[Frequency COlumn]) + 0

 Add this measure in your chart.

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 

[Anonymous]

@Tahreem24 

So I added the messure. Made sure "Show item with no data" is selected and added Sum of frequency to the Value field and number_of_emails to the axis feild. Still no 0 bar. 😞

When I go to the data section and filter on items with blank for "number_of_emails" I find ~200k rows, so the the data is defently there.

[Tahreem24]

If possible so can you please share some sample records or file by masking any confidential data. Or create a similar structure of dummy data.

[Anonymous · Accepted solution]

I actually got I to work in my dummy-file and I then comparied the settings between those two. Apparently, the right solution is to have the following settings...

Axis: number_of_emails, with "Show items with no data"

Value: number_of_emails, with "Count"

Default Summarization: on "Don't summarize" (number_of_emails)

Data settings: Relplaced nulls with zeroes

What I missed was that when I change "default summarization", for some reson, count switched over to sum. And the sum of a lot of zeroes is zero, so thats why no data was showing.

Thank you a lot for your help @Tahreem24 

[Tahreem24]

Good to know that your issue is solved. If you shared the details of your issue so probably I could have answered your query.

Then, later when my suggestion didn't work for you (show item with no data) that's why I asked for dummy file.

Good luck!