Forum Discussion
Calculated Field Formula
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
DAX measure for Commission with above Table1 as data source:
=CALCULATE (
SUM ( Table1[Profit] ) * 0.1,
FILTER ( Table1, Table1[Status] = "won" )
)
Lorenzo 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.
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.
- 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)
