Hi
I am having an issue with Null values in a pivot table that are affecting my calculations specifically because I need to hide them from the dimension. They cannot be showing in the front end view.
First let me explain what we have done here. In the database there is a hierarchy of Chains but because the users want to see different levels of Chains in the same dimension we have created a dimension that pulls in the specific ones they want to see from different dimensions. So what happens is that everything that doesn't fall into the ones we are wanting to see in our created dimension will show as a Null value which is why the Null values cannot be shown in the charts.
The calculation itself is not very complicated:
sum(Spend)/sum(total <Chain> Spend)
In this first table example the Null values are being included in the table and the calculations are giving me the correct percentages:
| Chain | Category |
| Chain 1 | 19.8% |
| Chain 1.1 | 10.3% |
| Chain 1.2 | 11.0% |
| Chain 2 | 0.4% |
| Chain 3 | 14.6% |
| Chain 4 | 3.3% |
| Chain 5 | 2.8% |
| Chain 6 | 0.4% |
| - | 100.0% |
But if I hide the Null values then I am getting these percentages (which are obviously only calculated from the Chains that are showing):
| Chain | Category |
| Chain 1 | 31.9% |
| Chain 1.1 | 16.5% |
| Chain 1.2 | 17.7% |
| Chain 2 | 0.6% |
| Chain 3 | 23.4% |
| Chain 4 | 5.3% |
| Chain 5 | 4.6% |
| Chain 6 | 0.6% |
I have tried a number of different things to try and get it to work as I need it to work but so far no luck so any suggestions that you might have would be appreciated.
Please don't ask me to post a sample of the data as I have posted what I can. This is obviously a much simpler version of what I am doing as both the Chain and the Category dimension are ones that we have created as I explained at the top.
Thanks
Colleen
