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

    Dividing a table calculation with an aggregated measure

    Ryan Blosser

      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.

      screen.jpg

      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?