I have a Pivot Table View that breaks down revenue per account and offering type. The rows are account and the columns are offering type and then offering. Doing this calculation is pretty straightforward. This is what it looks like:
| | Offering Type 1 | | | | Offering Type 2 | | Offering Type 3 |
| | Offering 1 | Offering 2 | Offering 3 | Offering 4 | Offering A | Offering B | Offering |
| Account 1 | - | $41,000 | - | - | - | - | $15,026 |
| Account 2 | - | - | - | $850 | - | - | $8,027 |
| Account 3 | - | - | - | $1,200 | - | - | $2,048 |
Note that I am not showing every possibility of revenue per account. I am limiting it to certain categories where the Offering Type is defined. For the categories I am not showing, the Offering Type and Offering is NULL in the data. I have a simple revenue formula that is:
Sum({<Country={'US'} >}revenue)
Now, I want to add a second calculation within this pivot table that shows the proportion of revenue each offering has relative to the total amount of revenue per account, even outside the categories shown. To do this, I just need to start with calculating the total revenue per account. What I would think to do is to do a calculation where I ignore the offering dimensions. I looked online and discovered how to use the TOTAL construct within the set analysis. I modified my Set Analysis to instead ignore everything but Account and Country:
Sum({<Country={'US'} > TOTAL <Account, Country>}revenue)
This produces an unexpected result, because it only showing the total per account of the data that is shown, and not all data, where Offering Type and Offering are NULL. Here's an example, adding that second calculation to the table:
| | Offering Type 1 | | | | | | | | Offering Type 2 | | | | Offering Type 3 | |
| | Offering 1 | | Offering 2 | | Offering 3 | | Offering 4 | | Offering A | | Offering B | | Offering | |
| | Rev | Total | Rev | Total | Rev | Total | Rev | Total | Rev | Total | Rev | Total | Rev | Total |
| Account 1 | - | - | $41,000 | $56,026 | - | - | - | - | - | - | - | - | $15,026 | $56,026 |
| Account 2 | - | - | - | - | - | - | $850 | $8,877 | - | - | - | - | $8,027 | $8,877 |
| Account 3 | - | - | - | - | - | - | $1,200 | $3,248 | - | - | - | - | $2,048 | $3,248 |
You will see that the Total value is the same for all non-null cells per row (i.e. per account). For example, in the first row, the value under Total is $56,026, which is the sum of all of the individual Rev columns, $41,000 + $15,026, and so on.
Now, the problem can be overcome by going back and checking "Include Null Value" on both the Offering Type and Offering dimensions. The Total column now shows the correct, expected value:
| | - | | Offering Type 1 | | | | | | | | Offering Type 2 | | | | Offering Type 3 | |
| | - | | Offering 1 | | Offering 2 | | Offering 3 | | Offering 4 | | Offering A | | Offering B | | Offering | |
| | Rev | Total | Rev | Total | Rev | Total | Rev | Total | Rev | Total | Rev | Total | Rev | Total | Rev | Total |
| Account 1 | $328,402 | $384,428 | - | - | $41,000 | $384,428 | - | - | - | - | - | - | - | - | $15,026 | $384,428 |
| Account 2 | $204,670 | $213,547 | - | - | - | - | - | - | $850 | $213,547 | - | - | - | - | $8,027 | $213,547 |
| Account 3 | $94,270 | $97,518 | - | - | - | - | - | - | $1,200 | $97,518 | - | - | - | - | $2,048 | $97,518 |
You will again see that the total is the same for all non-null cells per row (i.e. per account).
So I have fixed one problem and then created another one. What I really want is to be able to
- calculate the total revenue per account
- while hiding the data where Offering Type and Offering are NULL
- on a pivot table
So my question is, how do I use totals in the set analysis to include null dimension values in the calculation, even when the null dimensions are not included in the view?
