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

# Need a different calculation in totals

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.

• ###### 1. Re: Need a different calculation in totals

Is it possible to do this calculation?

• ###### 2. Re: Need a different calculation in totals

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