No data presented where no rows exist

[Anonymous]

We are looking to have a PBI where it shows every day in the row (1-31) with categories as columns (cat1, cat2, cat3 etc) then a count of every item and if no sales were made then show 0. (see current/desired outcomes in table 3 and 4)

The issue we are having is that if we sell no items on a day or a category in that month then it will not show the column/row. (see table 2)

The data we are using is a line for each item which holds the category name and date/time of sale, therefore, if nothing is sold on the 3rd, then there is no data to show. (see table 1)

Table 1 - Data

Date	Day	Item	Category
01/11/2019	1	1	1
02/11/2019	2	2	3
05/11/2019	5	3	1
05/11/2019	5	4	1
05/11/2019	5	5	1

Table 2 - Visualization

Rows	Day
Columns	Category
Values	Count of Items

Table 3 - Current Outcome (Matrix)

Day	Cat1	Cat3
1	1
2		1
5	8
7		3

Table 4 - Desired Outcome (Matrix)

Day	Cat1	Cat2	Cat3
1	1	0	0
2	0	0	1
3	0	0	0
4	0	0	0
5	8	0	0
6	0	0	0
7	0	0	3

[pforrer7 · Accepted solution]

Hi @Anonymous,

If you don't have a Category table create one and relate it, then show the new category field on the columns of the matrix and then build a count measure like this one:

Item count = COUNT('Table'[Item])+0

Note the +0 at the end, which makes Power BI show zeros where there is no data.

Let me know if this works for you.

[Anonymous]

@pforrer7  thank you! 😊