3 Replies Latest reply on May 1, 2018 11:34 AM by Deepak Rai

    Calculation with Measures containing No Data

    james.evans.10

      I have a fairly large data set that is unfortunately sort of messy. To get it into the format I needed I had to create a calculated field for each of the categories I needed; in the sample workbook I included you'll see what I mean. I have a Dimension called "Result Number" and it contains the results of 36 different tests for each Sample ID. To turn each test into a Measure I had to create a calculated field that looks like: IF [Result Number] = 'Result 1' THEN [Result Number] END. I did it for the first 5 in the sample workbook. Next I needed to create a calculation that looks like this: { FIXED [Sample ID] :SUM([Result 1]*4) + SUM([Result 2]*10) + SUM([Result 3]) + SUM([Result 4]*9)}. I called that formula "Calculation" in the workbook.

       

      The issue I'm having is that when there is no data for one of those results it is turning everything blank for that Sample ID. You'll see in Sheet 1 that the results are blank for Sample ID 1 and 3. So my question is how can I get the formula to work so that if one of the Result numbers contains no data it simply adds up the remaining Results in the formula.

       

      I tried to change all cells with no data into zeros using: zn(lookup(sum([Result Amount]), 0)). but then I cannot use that in the first formula (IF [Result Number] = 'Result 1' THEN [Result Number] END) because it is an aggregated measure.

       

      I hope this wasn't too confusing of a question! Any insight would be appreciated!

       

      Thanks