Pivot Table - Calculated Field

I need to add the following formula in a calculated field into a pivot to provide me with some rate and mix calculations.

=(sales x (Margin% - Total Margin%))-(Sales / Total sales) and so on...

Total above is the grand total for the column. All the above items are on my pivot via a local source data. However I can't figure out how to divide a cell by the grand total for for that column.

I am using Office 2010 64 bit on Windows 7. Any guidance would be greatly appreciated.

Regards

Hi

Thank you for the response. I have now switched to Power Pivot however I am not familiar with the dax formula which will do this for me.

Say row 3, 4 & 5 have values in them (in the pivot) and row 6 has a grand total. What formula would I use to divide row 3 by row 6?

Much appreciated.

Thank you for your response. Much appreciated however I have another question pls.

On the pivot, I have set up a new measure called GP Margin =(iferror(sum([TMTY FM£])/sum([TMTY Sales]),0)

What would my formula be if I wanted to do the same here i.e. divide a cell by the grand total? I keep getting the following error (The 'SUM' function only accepts a column reference as an argument.)

Any suggestions please?

Hi Romperstomper

Just tried this formula =SUM(Table1[TMTY FM%])/CALCULATE(SUM(Table1[TMTY FM%]),ALL(Table1)) and it results in the following error - The 'SUM' function only accepts a column reference as an argument.

FM% here doesn't come from the table, it is calculated as a measure from other data in the table. Could this be causing the issue?

Formula for the measure is =iferror(sum([TMTY FM£])/sum([TMTY Sales]),0)

TMTY - This month this year
TMLY - This month last year

Rate and Mix calculations - quiet a complex formula and the idea is that if anyone selects any report filter on the pivot, it works out the rate and mix calculations accordingly - at the moment I have to copy and paste the formula and ensure it is working every time as the number of rows are dynamic and change everytime the report filters change.

It is as a percentage in the end - but I think the formula above is now working. Basically the formula is nested as it multiplies 6 items and then subtracts 2 to give the final result - I have managed to nest 2 items in the measure and it's working so far...Will try and finish the remainder and see what happens.

Hi Romperstomper

Thank you for your help. I have managed to get everything working however one final question pls...

Formula I need help with is this ->>> =calculate([Sum of TMTY Sales],all(Table1))
Here the:
Sum of TMTY Sales - Is the name of the pivoted field
TMTY Sales - Is the name of the column in Table 1

In my raw data I have data for multiple months (lets say TMTY total is £1m in the raw data tab). If I do not use any filters in excel this formula gives me the right result i.e. £1m in the above example.

However when I filter on months or region etc, the value on the pivot table changes (let's say it is now £200k) however the result of this formula still brings back £1m in total.

Is there a way this formula can only bring back the totals based on the items I have sliced / filtered on the pivot table rather than the total from the raw data in the PowerPoint data window (i.e. £200k instead of £1m)? BTW I am using Excel 2010 32 bit.

Thanks in advance.

Hi RomperStomper

Below is a snapshot of the table

Untitled.png

I want the formula to divide E4 by E15, then E5 by E15 and so on. And I want it to work when different pivot slicers or report filters are selected as the all option takes all items in the raw data and doesn't consider what filter items have been selected.

Thanks for your help .

I need it as a measure as the formula on the screenshot is just the first bit. The complete formula is below and the "as % of column total" will not work...

Untitled 1.png

When I use "=SUM(Table1[TMTY Sales])" the result is the same as cell E4 i.e. £4,084 yet I want it to show £1,774,802 (cell E15) as shown below

Untitled 3.png

Think about it this way - £4,084 / £4,084 = 1.0 yet the correct formula needs to be £4,084 / £1,774,802 = 0.0021

Does this make it clearer?

Hi

Complete snapshot below.

Untitled 6.png

Thanks a lot. All done and working

Great support. A++++