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

Arya,

Ex:

Azul $1.890 $5.50 15%

Azul $1.830 $6.20 15%

Azul $3.500 $8.50 15%

When I create the calculated field (pivot table) COMISSION

Formula --> (Total Invest + Corret)*AVERAGE(Status)

Instead of getting 15% as average, I'm getting 45%

thanks

Answer 2

Please upload your dummy file to Onedrive and share the link here.

Answer 3

ITS there

https://1drv.ms/x/s!AmZPY\_vKW1kHdNVMQjOTS5169Mc?e=bSCaAg

Answer 4

"Formulas operate on sum totals, not individual records Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula."

https://support.office.com/en-us/article/calcul...

Hence, SUM of total gets multiplied by SUM of Commission giving that results.

Your requirement can't be fulfilled through pivot table calculated field. Power pivot is the way to go as suggested by Ashish.

Answer 5

Hi Licia

I don't think you need PowerQuery or PowerPivot for this.

What do the values in Column G (CORRET) represent?

Thanks.