OBIEE and fake totals - weighted avg.

In OBIEE you can set a total line, on any level of table and pivot. In the example the total is at Year and Grand Total level.

The result of the total depends on the aggregation setting in the formula. If we will change the Revenue's Aggregation Rule (Totals Row) from Default(sum) to Average, we will have a different Totals result:

This is great as long as you are happy with the options you have 

in the list. 

In my case I was requested to show weighted average in the totals line.   

What does it mean?

If I sold 1000 items for $1 each, the sale is $1000.

If I sold 1 item for $1000, the sale is $1000 as well.

The average of the sale is (1000+1000)/2 = 1000

The weighted average of the sale is (1000+1000)/(1000+1)=1.998002

There is no default option of weighted average in OBIEE. To make it totally impossible, the source table was aggregated (I don't have 1000 lines of 1 but only 1 line of data). So I decided to fake it.

My original table had 4 columns: Name, code, count and value. For the first line of the above example the count is 1000 and value 1.

I wanted additional column that multiplies count and value at the transactional (not aggregated) level. I could require it as a change in the data source, but I decided to create a calculated column.

In Administration I added a column named multip where the "column source" is "derived from physical mapping".

 There I created a calculation count*value.

The aggregation of the column is Sum.

 

Next I created an Analysis based on those 5 columns.

How to add the total for each name? I selected "Combine results..." at the criteria and selected "Union All":

In the second criteria I have the following:

In the name column, the name column.

In the code column, the string 'Total'.

Then measures (I removed folder names, for clarity):

sum("count1" by "name1")

sum("value1" by "name1")

sum("multip" by "name1")/sum("count1" by "name1")

Now we need the final finishing:

I need to sort it first by name and then by code. But how can I be sure the string 'Total' will be the last one for each name? I decided to concatenate a space before each code (the formula   ' '||"code").

Last step is to change the color of cells in the total line using "Conditional Format", each time the code='Total'.

The result is:

One last problem: we can't allow dashboard user to sort the table or play with it. So at the Analysis properties

I will prevent all interactions: