Pivot Table with Multiple Currencies Summing to One Currency

[MoreQsThanAs]

Hi all - I have an worksheet style database in Excel that contains information on several firms over several periods. Each measure is in the firm's native currency (USD, CAD) and I also have a column with the converted currency (CAD). In my Pivot Tables, I would like the ability to sum to one currency (not a mixed currency), but have the entity values reflect the native currency. Ideally, the when the measures are summed, the $U would convert to $C for the total. The Pivot would look like this:

Measure 1 Measure 2 Measure 3
Entity A (USD) $U $U $U
Entity B (CAD) $C $C $C
Entity C (CAD) $C $C $C
Total (CAD) $C $C $C

I've tried to do this with custom Pivot formulas and If statements without success. Any suggestions?

Thanks!