I need to display zero values in a pivot table and I can't find a solution that works. I've attached a very simple example: a pivot table with two dimension, month and category. Category A is missing a value for month 2017-03. So if I select A, 2017-03 won't be displayed. I would like all months to show regardless of which category is selected. However, only the months that are selected should be displayed. So always showing everything doesn't work either.
To make matters worse, adding dummy values in the data model isn't really an option. Is there any way to achive what I want without modifying the data?
Unfortunately that means I can't filter on the months, it always shows all months. Generating missing data isn't really a good option in this situation, for various reasons. One reason is that for some calculations I count distinct values of a key. I can't really add a dummy key since then it won't be zero anymore. I also can't add a column with value 1 and just sum that since then it won't be distinct anymore.
That's true. I would however prefer to solve it without generating data since I would have to generate values for all possible combinations of 5-10 fields on every date over 3 years. That sounds like a headache to me, unless I'm completely missing something.
I have not came across a way to solve this problem without generating missing data. Everything I have seen is a sort of hack with its own problems. But having said that, there are many smart people on the community... and someone might be able to prove me wrong.