Using Totals in Set Analysis to Include Nulls Even when Null Values are not Included

[mplautz]

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?