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,
Writer, Director, Filmmaker