2 Replies Latest reply on Jun 22, 2012 1:24 PM by Daniel Carr

# Divide results from same summary field but with different filters for numerator and denominator

Hello,

We have the following issue:

• We have US States in ‘Rows’.
• We have Product Name in ‘Columns’
• Our summary info is ‘Sales’

So, grid looks like this:

 State Books Computers New York 200 500 California 400 1000

We would like to divide the Books by Computers for each
state.  i.e. For NY 200/500.  For CA 400/1000.

We cannot figure out how to create a formula which does this
since it is Sales/Sales (where the numerator and denominator just have
different filters.)

Thanks,

Dan

• ###### 1. Re: Divide results from same summary field but with different filters for numerator and denominator

Hi Dan,

This can be done a couple of ways, the first is to create a calculated field that uses a table calculation similar to the following:

LOOKUP(ZN(SUM([Sales)), -1)/ ZN(SUM([Sales]))

Place this on the view, right click and select Compute Using-->Table(Across).

Another option is to create 2 calculated fields, one for each Product Name.

if [Product Name]='Books' then [Sales] end

if [Product Name]='Computers' then [Sales] end

Then, create a calculated field that uses these two calculations:

sum([Books Calculation])/sum([Computers Calculation])

Using this example, Product Name will need to be removed from the Columns shelf, and these three calculations should replace it.

Hope this helps!

-Tracy

• ###### 2. Re: Divide results from same summary field but with different filters for numerator and denominator

Thanks Tracy!

Worked perfectly.

Dan