9 Replies Latest reply on Jun 23, 2016 9:17 AM by Yuriy Fal

# Grand Total and Calculations

Hi all,

I recently posted a question (Top N with "Others" for Multiple Measures ) to get a table that displays Top N with "Others" and the ability to sort on different metrics. Now I am trying to add a Grand total to the bottom, but I need a single column to be a calculated measure based on the other two column's totals instead of the summation of every value in that column.

My table is set up as following:

Inventory (sum of total units), Sales forecast (sum of total untils), and days of supply/forecast (inventory divided by sales forecast). I want the grand total to sum the inventory and forecast columns, but then apply the calculation for days of supply.

Is this something that is possible?

Thanks!

• ###### 1. Re: Grand Total and Calculations

Hi Ryan,

This is Yuri (again :-).

Please show me the exact calculations

for your three Measures in question.

If they are "regular" aggregates,

then there should be no problem

with Grand Totals.

Yours,

Yuri

• ###### 2. Re: Grand Total and Calculations

Hi Yuriy,

If you remember the last question that I had, I have the table that has top N plus "others" and a parameter to sort by a chosen measure. My calculations are as follows:

Inventory -

IF [In Top N?] THEN SUM([Oh Cases])

ELSEIF [Show All Suppliers] THEN SUM([Oh Cases])

ELSEIF [Others?] THEN WINDOW_SUM(SUM([Oh Cases]), 0, LAST())

ELSE NULL

END

Forecast

IF [In Top N?] THEN SUM([90 Day Fcst])

ELSEIF [Show All Suppliers] THEN SUM([90 Day Fcst])

ELSEIF [Others?] THEN WINDOW_SUM(SUM([90 Day Fcst]), 0, LAST())

ELSE NULL

END

Days of Forecast

IF [In Top N?] THEN SUM([Forecast Days of Sales])

ELSEIF [Show All Suppliers] THEN SUM([Forecast Days of Sales])

ELSEIF [Others?] THEN (WINDOW_SUM(SUM([Oh Cases]), 0, LAST()))/(WINDOW_SUM(SUM([90 Day Fcst]), 0, LAST())/90)

ELSE NULL

END

The only issue that I am having is in the "Days of Forecast" grand total. This should be the grand total of inventory divided by the grand total of forecasted days of sales.

Thanks,

• ###### 3. Re: Grand Total and Calculations

HI Ryan,

What if you create another calculation like this:

[Inventory] / [Forecast] * 90

This could be your Days of Sales --

and I hope Grand Totals would be correct.

Yours,

Yuri

• ###### 4. Re: Grand Total and Calculations

Hi Yuriy,

That still produces the same values in the grand total line and does not sum the "others" into a single line (which is why I was using the window_sum function)

• ###### 5. Re: Grand Total and Calculations

Hi Ryan,

Please find the attached same workbook with Grand Totals --

and with [Top N and Other Price] calculated as above.

Please check if you calcs and pills on a view differ from the attached.

Yours,

Yuri

• ###### 6. Re: Grand Total and Calculations

Thanks Yuriy,

If I understand your book correctly you did the following:

Used a Window_Sum function for the "Top N" of Sales, Order Qty, and Shipping Cost and then used those calculated fields in your calculation for price?

I was calculating my "Days of Forecast" (your "price") based on the original data and not the "Top N" calculated field. I think this will work and do what I need.

• ###### 7. Re: Grand Total and Calculations

Hi Ryan,

Please, do calculate your [Days of Forecast 1]  as

[Inventory] / [Forecast] * 90

Place it on Measure Values shelf

side-by-side with your other Measures on a view,

activate Show Grand Totals and see the result.

Yours,

Yuri

• ###### 8. Re: Grand Total and Calculations

My Grand Totals are now working correctly.

Thanks for your help (again).

• ###### 9. Re: Grand Total and Calculations

Ryan, you're welcome.