Forum Discussion
How to show null values when in data table there's no such data
Hello!
I am working with a report where in the data the values with 0's aren't included in the data table. My problem is that I must portray in graph's also the 0 values.
Here is a much simplified table, where it's not portrayed that France has 0 oranges:
| Country | Product | Amount |
| Germany | Apple | 5 |
| France | Apple | 3 |
| Spain | Apple | 1 |
| Germany | Orange | 2 |
| Spain | Orange | 1 |
Is there a way how I can create data (France = 0 oranges) or show within PBI that France (and any other countries) is the outlier that has no values?
4 Replies
- Shaurya
Memorable Member
Hi ievazal,
In Power Query create a new table and insert the row for 0 values by using Enter Data option.
Append this table to your actual table and you'll get rows for 0 amount as well for specific countries and products.
Now add a table visual that uses Amount column as a filter. Set the filter to display data where value is 0.
Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Graphical ComparisonHi Shaurya !
So the problem with this is that... I will have 4k+ new rows added daily into this table, therefore I won't be able to physically go over everything and check for which countries have the 0 amount and then add those manually..
Would there be any DAX I could possibly use to make this doable?
- Shaurya
Hi ievazal,
Well in that case there has to be a list of all the countries and products. Since a 0 value means that there is no row for that combination of Country and Product, there should be a dimension that still has those names listed. In that case we can write formulas that check whether there are countries and products that do not have any value.
Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog: Forecast Period - Graphical Comparison
