Pivot Table Calculated Field using IF statement

Question

HI

I Want to calculate 15% over sales, IF The Status is a "Comission", it Status is "NO" is 0.

Status --> "Comission" or "NO"

Insert Calculated Field box:

Field Name --> IR 15%

Formula --> IF(STATUS="Comission",(Total Invest + Corretagem)*15%,0)

result is 0 for all items. 

thanks

Answer 1

Hello luciapernambuco

I am V. Arya, Independent Advisor, to work with you on this issue. The formula within calculated field will work only on the fields which you drag inside Values section. It won't work for Rows section fields. Hence, the workaround is to create a helper column and drag helper column also to pivot (for aesthetics purpose, you may need to hide this column itself)

Then you can use following formula (as per my helper column). Also notice single quote around Total Invest.

= IF('Helper column' >0,('Total Invest' +Corretagem )*0.15,0)

Sample file @ https://1drv.ms/x/s!AgiTQGHVw-zChCZbRdk_C70a2IV...

Answer 2

HI V.Aria, thanks a lot for your time and sample you created, very helpful. 

I understood the concept, so, I can't use the IF for a Text, only values. 

So, I made a change on my file, and instead of Status (text), I'm using the comission percentage, like, 0% and 15% (as an ex.)

Formula --> (Total Invest + Corretagem)*Status

It didn't work. Why is that? Any way around, or I should follow your suggestion?

thanks for helping out, have a nice day

Answer 3

Looks like you have dragged Status to Rows section. Drag it to Values section. You can use average applied on this so that value of commisssion doesn't change. Then your formula would work.

Answer 4

Hi,

An IF() function in a calculated field formula of a conventional Pivot Table will not work well.  You will have to use the PowerPivot.

Answer 5

HI AShish, thanks for helping out

I'm using a Excel 365 for MAC, don't have the power pivot function