2 Replies Latest reply on Jul 1, 2016 1:17 PM by Shinichiro Murakami

    Need a different calculation in totals

    chandu sai

      From the screenshot below:

      In "FinalCalculation" column total is the average of 'Q1&Q2' but I need total to be calculated based on formulas shown below.

      And the result should be like screenshot: TableB (Hightlighted row in yellow)

      Formulas Used:

      TotalsByHistory= (Totals/History)*365

      sum of NO.ofDAYSinQTR = Hardcoded values for each quarter

      FinalCalculations = (TotalsBYHistory)/(No.ofDAYSinQTR)

       

      Is it Doable?

      Workbook is attached.

      And Thanks in Advance.

       

       

      ScreenShot.PNG

        • 1. Re: Need a different calculation in totals
          chandu sai

          Is it possible to do this calculation?

          • 2. Re: Need a different calculation in totals
            Shinichiro Murakami

            I don't know what is the format of original data source, but assuming that's below.

             

            Starting from Pivot the data first.

             

            Then Create calculated field as different measure for each category of Total, History, and Number of days.

             

            [Totals]

            if [Calculations]="Totals" then [Pivot field values]end

            [History]

            if [Calculations]="History" then [Pivot field values]end

            [Number of Days]

            if [Calculations]="Avg. DaysinPeriod" then [Pivot field values]end

             

            Then create two more calculated field using above two.

            [TotalbyHistory]

            sum([Totals])/sum([History])*365

            [Final Calculation]

            [TotalbyHistory]/sum([Number of Days])

            Group Quarters

            [Half a year]

            if CONTAINS([Pivot field names],"Q1")

            or

            CONTAINS([Pivot field names],"Q2")

            then "Q1,Q2"

            elseif

            CONTAINS([Pivot field names],"Q3")

            or

            CONTAINS([Pivot field names],"Q4")

            then "Q3,Q4"

            END

            Then add Total.

             

             

            Thanks,

            Shin