3 Replies Latest reply on Nov 29, 2017 1:54 PM by Ryan Blosser

Dividing a table calculation with an aggregated measure

Hello, I am having a problem trying to get a total by taking a table calculation and dividing it by an aggregated measure.  I have data that is structured in a way such that certain rows need to be counted into two separate buckets (meaning if I have two groups of data, rows from group A with an Exception should also be in group B), so I used the Lookup function to deal with this.  In my case, it was easy because there are only two buckets, so one looks back 1 and the other looks forward 1 in the window.  This value was added to a field and this created another table calculation (maybe I can manipulate this somehow?).  But I cannot take this new value and divide it by another value, because if I do, there is nothing that shows up in the cell.

So this formula here:

if min([Biz]) = max([Biz]) then ([QTD Total]+sum([QTD Rev]))/sum([QTD Quota]) else sum([QTD Rev])/sum([QTD Quota]) end

Does not work for anything but the grand total (the TRUE condition doesn't work).  I'm sure this probably wasn't the best way to even structure the data (and I suppose any other trick would be helpful), but that was what I came up with.  How can I make that attainment have a value everywhere?

• 1. Re: Dividing a table calculation with an aggregated measure

Hi

You need to account for nulls with zn()

it will return

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 2. Re: Dividing a table calculation with an aggregated measure

Thanks