
1. Re: Grand Total and Calculations
Yuriy Fal Jun 20, 2016 1:04 PM (in response to Ryan Meehan)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
Ryan Meehan Jun 21, 2016 7:12 AM (in response to Yuriy Fal)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
Yuriy Fal Jun 21, 2016 10:44 AM (in response to Ryan Meehan)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
Ryan Meehan Jun 22, 2016 7:23 AM (in response to Yuriy Fal)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
Yuriy Fal Jun 23, 2016 8:21 AM (in response to Ryan Meehan)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
Ryan Meehan Jun 23, 2016 8:36 AM (in response to Yuriy Fal)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
Yuriy Fal Jun 23, 2016 8:54 AM (in response to Ryan Meehan)Hi Ryan,
Please, do calculate your [Days of Forecast 1] as
[Inventory] / [Forecast] * 90
Place it on Measure Values shelf
sidebyside with your other Measures on a view,
activate Show Grand Totals and see the result.
Yours,
Yuri

8. Re: Grand Total and Calculations
Ryan Meehan Jun 23, 2016 9:00 AM (in response to Yuriy Fal)My Grand Totals are now working correctly.
Thanks for your help (again).

9. Re: Grand Total and Calculations
Yuriy Fal Jun 23, 2016 9:17 AM (in response to Ryan Meehan)Ryan, you're welcome.