Calculated Field Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3105460%22%20slang%3D%22en-US%22%3ECalculated%20Field%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3105460%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20pivot%20table%20which%20shows%20for%20certain%20jobs%20what%20the%20price%2C%20costs%20and%20profit%20are.%20Our%203rd%20Party%20Supplier%20then%20gets%20a%2010%25%20commission%20on%20jobs%20that%20were%20actually%20booked.%3CBR%20%2F%3ENot%20all%20the%20jobs%20that%20are%20entered%20on%20the%20system%20get%20booked%2C%20but%20the%20report%20needs%20to%20reflect%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20everything%20sorted%20in%20the%20report%2C%20except%20the%20calculated%20field%20which%20will%20have%20a%20heading%20of%20%22Commission%22.%3C%2FP%3E%3CP%3EThe%20Profit%20field%20is%20called%20%22Profit%22%2C%20the%20Status%20Field%20is%20called%20%22Status%22%20and%20in%20the%20status%20field%20the%20entries%20are%20either%20won%2C%20lost%20or%20in%20progress%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20the%20formula%20to%20be%20an%20if%20the%20Status%20%3D%20won%20then%20do%20profit%20x%2010%25%2C%20if%20not%20show%20a%20zero.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20%3A%3CBR%20%2F%3E%3DIF(Status%3D%22won%22%2C%20SUM(Profit*10%25)%2C0)%3CBR%20%2F%3EBut%20for%20some%20reason%20it%20doesn't%20recognise%20the%20won%20part%20and%20shows%20zero's%20for%20every%20line.%3CBR%20%2F%3EAny%20help%20would%20be%20very%20much%20appreciated.%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3105460%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3106694%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20Field%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3106694%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1294658%22%20target%3D%22_blank%22%3E%40wyptaj%3C%2FA%3E%26nbsp%3B%2F%20Paul%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20my%20knowledge%20the%20%5BStatus%5D%20field%20requires%20Numeric%20values%20(not%20Text%20ones)%20for%20such%20formula%20to%20work%20in%20classic%20Pivot%20Tables%3CBR%20%2F%3EIf%20you%20run%20a%20Windows%20version%20of%20Excel%20there's%20probably%20a%20solution%20with%20Power%20Pivot%20-%20basic%20example%20below%20and%20attached%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344691i7A181953F2875E65%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot.png%22%20alt%3D%22Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EDAX%20measure%20for%20Commission%20with%20above%20Table1%20as%20data%20source%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCALCULATE%20(%0A%20%20%20%20SUM%20(%20Table1%5BProfit%5D%20)%20*%200.1%2C%0A%20%20%20%20FILTER%20(%20Table1%2C%20Table1%5BStatus%5D%20%3D%20%22won%22%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3106826%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20Field%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3106826%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3BThank%20you%20for%20that....sadly%20that%20is%20way%20above%20my%20skill%20level....as%20I%20can't%20find%20how%20you%20add%20in%20DAX%20let%20alone%20get%20that%20to%20work.%20But%20thank%20you%20for%20answering%20my%20question...I%20didn't%20realise%20it%20only%20worked%20on%20numeric%20as%20opposed%20to%20text.%3CBR%20%2F%3EMany%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello All, 

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.

 

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 progress

I want the formula to be an if the Status = won then do profit x 10%, if not show a zero.

 

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 thanks

Paul

4 Replies

Hi @wyptaj / Paul

 

To my knowledge the [Status] field requires Numeric values (not Text ones) for such formula to work in classic Pivot Tables
If you run a Windows version of Excel there's probably a solution with Power Pivot - basic example below and attached

Screenshot.png

DAX measure for Commission with above Table1 as data source:

=CALCULATE (
    SUM ( Table1[Profit] ) * 0.1,
    FILTER ( Table1, Table1[Status] = "won" )
)

@L z. 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.

I understand the challenge if you're not familiar with Power Pivot/DAX
If 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)

@wyptaj 

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

image.png

which gives

image.png

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