Forum Discussion
No data presented where no rows exist
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 |
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])+0Note the +0 at the end, which makes Power BI show zeros where there is no data.Let me know if this works for you.
2 Replies
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])+0Note the +0 at the end, which makes Power BI show zeros where there is no data.Let me know if this works for you.pforrer7 thank you! 😊