OBIEE - Comparing performance to peers - next step

In the post OBIEE - Comparing performance to peers I covered 2 options to create an analysis where we compare the performance of particular group member, with the entire group. We used 2 methods, Option 1: with aggregation on the entire group (and selection step) and Option 2: with filter function on the group member.

I promised to try and improve it. They are few thing I'd like to improve:

• Compare the member to the rest of the group and not entire group (subtract  the member data from the rest).
• Comparing values or % of total and not Ranking. 
• Limiting presentation by minimal % and not specifically top 10.

The first option described here is not connected to the other 2.

We start with this:

Compare to "All the group" or "the Rest of the group"

In the post we compared the revenue of member (specific Company) with Revenue of the entire group (all members). In small groups the influence of each member might be significant. In our example there are only 3 companies. I believe our comparison would be better if we remove the specific company Revenue from the total.
We can subtract the member value from the group total to achieve it. This should be done with the second option - the filter function. Why?

Because the first option of the previous post would end up with problematic result.

Changing from this:

The basic columns: Company, Product, Revenue

Calculations:

• Sum(Revenue by Product) = the total Revenue for each Product
• Rank (Sum(Revenue by Product)) = The Ranking of each Product Revenue for the entire data
• Rank(Revenue by Company) = The Ranking of each Product Revenue for each Company

We move to this option, that doesn't work:

The basic columns: Company, Product, Revenue

Calculations:

• Sum(Revenue by Product)-Revenue = the total Revenue for each Product of the rest
• Rank (Sum(Revenue by Product)-Revenue) = The Ranking of each Product Revenue for the entire data of the rest of the group
• Rank(Revenue by Company) = The Ranking of each Product Revenue for each Company

Since there is a problem with the Total Ranking order, where are Ranks 2 and 3?

 In the previous example the grand total for each company of a specific product was the same so the relevant Ranks were the same. Now since we subtract different Revenues, the Ranks are per combination of product and company!

This problem does not happen in the Filter Function version, because we subtract the same value for each product. 

So we replace this:

• Product
• FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' ))
• Rank(FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )) )
• Revenue (we don't need the sum(revenue by Product since there is no company column)
• RANK(Revenue)
• The relevant case statement

With this:

• Product 
• FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )) 
• Rank(FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )) )
• Revenue - FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' ))
• RANK(Revenue - FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )))
• The relevant case statement

Remember to change the analysis filter to the updated Rank<11.

Comparing % of total and Not Ranks

Comparing Ranks is a problematic practice. In many cases we might miss valuable information this way. For example: the top 3 products are responsible for 90% of the revenue. So the step from rank 3 to rank 4 might be only 1 in ranks, but is a very big in the actual revenue. They are 2 options I prefer:

• Comparing revenue to the average revenue per product of the others.
• Comparing to % of Total products.

The second option is better in our case since the companies might be of different sizes.
To make things readable I will start from the previous post example of comparing to all group members.

I'll add 2 columns:

This is the Option 1, Selection Step solution:

• The % of Revenue per product of all Companies: 100*sum(Revenue by Product)/sum(Revenue)
• The % of Revenue per product of current Company: 100*Revenue/sum(Revenue by Company)

This is the Option 2, Filter Function Solution:

• The % of Revenue per product of all Companies: 100*Revenue/sum(Revenue)
• The % of Revenue per product of current Company: 100*FILTER("Revenue" USING ("Company" = 'Genmind Corp' ))/Sum(FILTER("Revenue" USING (""Company" = 'Genmind Corp' )))

Now we can decide what is the percentage difference we want to emphasize.
For example I'll create a column that subtracts the 2 new columns and mark the cases there is a difference of over 0.4% (or anything that works for you).

Now we can hide/exclude/remove any column we don't need or don't want to show.

Using minimal % instead of top 10

The selection of top 10 is somewhat random, since not all data is evenly distributed like in our case.I'd rather use a specific % of total. For example 4.5%.

Compare to "All the group" or "the Rest of the group" - again

Now we can return to the selection steps option again, since we don't depend on Rank any more. The calculations of the previous type work fine with percentage options.