I have created a dashboard based on an ssas cube with accounting data. The top sheet shows the actuals based on an account intelligence hierarchy and is used to filter sheets below.
The top sheet looks a bit like this:
The sheet shows the actuals with the above structure on the rows and several diviions on the columns. Obviously, you can simply add the actuals for 'income' and 'cost' to get to the 'gross result' as you can't sum the 'gross result' with the 'other costs' to het the 'Net result' either. This is where the account intelligence comes in. In the SSAS cube the dimension is specified in such a way that the it 'knows' how th add the different account lines.
When I click on on the 'total income' line for a given division, I want the sheets below to show only the actuals for 'total income'. When I select the entire line, it will sum all divisions. This all works fine. Now when I click on the column (a division) a select all the account lines and as a result , the sheets below show the sum of each of the account line. This - obviously - is not the result I was hoping for because the amount is complete nonsense.
The best solution would be if I can make the sheets below show the 'net result' which is the 'total' of the selected result. Any idea? Another solution might me to make the headers 'unselectable'. I could use a textual sheet for that and hide the headers but is there is some other way to do so?
Any thoughts/help would be appreciated! Kind regards