QuickBooks Custom Fields in PowerPivot | CLEARIFY

How to Use a Custom Field in a Calculation

Custom fields are stored as text inside QuickBooks, regardless of the mask you assign to it.

Using a text based custom field in an Excel calculation is akin to oil and water.  The text has to be converted to a number, so that it can give you the proper result, and if there is any character in the text field that doesn't look like a number, the results are rejected..  (Read more about custom fields here.)

In this guide we will show you how to transform the text in a custom field to a numerical value.

The overall steps are: 

1. Open the data model
2. If needed - unhide the custom field to make available to your data model
3. Change the data type from text to decimal number
4. Format the field to your needs
5. Close the data model
6. Find the newly available field and use in your pivot table

From the PowerPivot Tab in Excel, click on the "Manage" Data Model icon.

In the data model highlight the Document Attributes Tab. If you are needing a custom field other than the ones default displayed, unhide it.

Highlight the column (custom field) and click the Data Type option to change it to Decimal Number.

If you have text data that cannot be converted to a decimal format you will receive an error similar to the following.  In this case, it will be necessary to go back into QuickBooks and change those entries in order to continue.

If result is successful, the Data Type will change, and then you can change the Format to your desired choice.

Close the model when finished.

The custom field is now available for use in a calculated measure.