Forum Discussion
Help with a calculated field in a PivotTable
Hi, I am trying to write a formula for a calculated field inside a Pivot Table. I want to return the value 1 if the row value is equal to "New York", and zero otherwise. My formula is
=IF(ISNUMBER(FIND(City, "New York" ) ),1,0 )
However, the formula always returns zero. Please see the attached screen shot.
Thanks, Ben.
3 Replies
In addition to Wyn Hopkins . I have practically now experience in using calculating fields with texts and can't say why such combination doesn't work and what are workarounds if any. You may test much more simple calculated field
= LEN(City)it always returns 1.
If to work with data model and you would like to use SEARCH or FIND to recognize, for example, "City of New York" as NY, these functions work bit different way compare to Excel. They have 4th parameter to return if_not_found value. Without details, if don't use you'll receive an error for entire column/measure.
In data model I'd add calculated column
as
=IF(SEARCH("New York",Table1[City],,0),1,0)or
=MIN(SEARCH("New York",Table1[City],,0),1)If with measure you may check filter context as Wyn suggested or iterate row context wit ...X functions as
IsNY:=IF(SUMX(Table1,SEARCH("New York",Table1[City],,0)),1,0)That looks like
SergeiBaklan , Hi, thanks for all of the help, I ended up doing this in DAX.
I'd strongly recommend you start using DAX in the Power Pivot model if you want to write formulas in Pivot Tables. So much more flexible and powerful than Pivot Table calculated fields
The DAX would be something like this
If you've not used DAX before it is very different to how Excel formulas work
If this is of interest I can explain more.
