How to solve the #NUM! error when adding a calculated column to a Sum in PowerPivot (Excel)

Seems like my last few posts are a bit unusual fare, but these are the things with which I’ve been debating. Might as well share the solution in case anyone’s running into the same problem.

So here I am using PowerPivot for a report under Microsoft Excel 2010. It’s a sales report, but it is in pesos. I added a calculated column so I can divide the Amount column by the exchange rate of that day, and obtain the Amount in USD.

For some reason when I add that new field to the Sum section of the PivotTable all I get are #NUM! errors. There had to be a way to display/sum those in PowerPivot, otherwise what would be the point?

So, I finally solved it by accident.

First I tried adding a ISERROR function, but that didn’t solve it.

Then, I saw this little button I hadn’t noticed before in the Design tab of PowerPivot which said Mark as Dates Table (or something like that — Excel interface is in Spanish) — and behold, suddenly my calculated columns worked! Ta-daaaaah!

Hope that helps,
Val Gameiro
Writer, Director, Filmmaker