2 Replies Latest reply on Jun 8, 2017 9:24 AM by Nhung Nguyen

    Calculated field working weird

    Nhung Nguyen

      Any one please explain for me this issues:

      The Amt PC = Amt org.est * Job PC

      However, the result is totally off with cost code 15-14 and 15-15 while others are correct. 

      Below are my formula for all fields:

      Any help would be greatly appreciated

        • 1. Re: Calculated field working weird
          Okechukwu Ossai

          Hi Nhung,

           

           

          It is always difficult to look at an image and say what is wrong with a calculation. Mocking up some dummy dataset is helpful in this case.

          However, the issue is probably due to your data being aggregated somewhere. There is something systematic going on. For example, 243280/107442 = 13516/58083 = 2.264. Can you see that Amt PC for both Cost Codes is inflated by the same amount. 243280 is exactly 3 times what the correct answer should be. This makes me believe that you have 3 records each for 15-14 and 15-15. So, Amt PC sums up the 3 records.

           

          What is the aggregation type for [Amt PC] when you drag it to the Row or Column shelf? If it is SUM([Amt PC]), then you can either go to the Measures pane, right click on the field and select Default properties   -   Aggregation   -   Average. Or better still, right click on the field name on the row or column shelf and change Measure Aggregation to Average.

           

          If that didn't work, then try changing SUM([Amt Org.est]) in your [Amt PC] formula to AVG([Amt Org.est]) or MIN([Amt Org.est]) and see what happens.

           

          Let me know how it goes.

          Ossai

          • 2. Re: Calculated field working weird
            Nhung Nguyen

            You are right. Those cost code have multiples category applied. Thank you so much for all of your time

            .