Calculated Field Formula

Question

Hello All,&nbsp;I have an excel pivot table which shows for certain jobs what the price, costs and profit are. Our 3rd Party Supplier then gets a 10% commission on jobs that were actually booked.Not all the jobs that are entered on the system get booked, but the report needs to reflect that.&nbsp;I have everything sorted in the report, except the calculated field which will have a heading of "Commission".The Profit field is called "Profit", the Status Field is called "Status" and in the status field the entries are either won, lost or in progressI want the formula to be an if the Status = won then do profit x 10%, if not show a zero.&nbsp;I have tried :=IF(Status="won", SUM(Profit*10%),0)But for some reason it doesn't recognise the won part and shows zero's for every line.Any help would be very much appreciated.Many thanksPaul

lorenzo · Answer

Hi&nbsp;wyptaj&nbsp;/ Paul&nbsp;To my knowledge the [Status] field requires Numeric values (not Text ones) for such formula to work in classic Pivot TablesIf you run a Windows version of Excel there's probably a solution with Power Pivot - basic example below and attachedDAX measure for Commission with above Table1 as data source:=CALCULATE (
    SUM ( Table1[Profit] ) * 0.1,
    FILTER ( Table1, Table1[Status] = "won" )
)

wyptaj · Answer

Lorenzo&nbsp;Thank you for that....sadly that is way above my skill level....as I can't find how you add in DAX let alone get that to work. But thank you for answering my question...I didn't realise it only worked on numeric as opposed to text.Many thanks.

lorenzo · Answer

I understand the challenge if you're not familiar with Power Pivot/DAXIf your workbook doesn't contain sensitive data could you attach it to your next reply so one can have a look at it and put in place the solution (assuming one exists)

sergeibaklan · Answer

wyptaj&nbsp;
My understanding that in cached PivotTable formulae in calculated field don't work with labels, only with value fields.
As workaround you may add calculated item to Status

which gives

Not exactly what is required, some approximation. To be flexible you need to work wit data model.

Forum Discussion

Calculated Field Formula

4 Replies

Resources