Power BI SUM Anomaly

[Anonymous]

Hi everyone,

I have a table with financial data about projects, and those are in local currencies. I also have a table with hystorical fx rates so i wanted to create a posibility for the user to see the financial data in a visualization but also be able to choose what rate should the conversion be made at. So what i did: i created a table with distinct currency codes that acts as intermediary for the financial data table and fx rates table, so both of them have relationship with this table. Next step: create a measure that is SUM of the fx rates from the fx rates table, create a measure that is SUM of the metric i want to see in USD. Then a new measure (metric_USD) that is SUM(fxrates)*SUM(metric).

So when i create a visualization i can attach a slicer to it and if i select only one period (from the FX rates table), it will multiply my metric with the rate from that period.

But the strange thing is that for some metrics it works correctly (aside the subtotals, that are tottaly dumb random numbers, i don't know how they are calculated), and for some, it just adds up more and more with every period increase, so the final numbers are way to big.

What could be the cause of this?

[amitchandak]

You should try creating a column and then use the sum

metric_USD= fxrates*metric

As they are in a different table. You might have to create a column to bring the fx rate from another table.

[Anonymous]

@amitchandak If i bring the fx rates to the main financial data table I wouldn't be able to use the slicer anymore, as it permits me to select the period for the rate to be used in the conversion.

[amitchandak]

Can you share a sample pbix, after removing sensitive information?

[Anonymous]

Well, the problem actually is based on sensitive data, and 90% of the pbix is sensitive data....any other hint that I can give you ? Maybe how te relationships are made ?

[amitchandak]

Can you add a sample rate table for a few days and share back with the formula you are trying to create.

https://www.dropbox.com/s/unfqa025ca7p3vw/CompareRange_timedim.pbix?dl=0

Use sales as the amount you want to convert.

[Anonymous · Accepted solution]

You proabably have a join issue. I would suggest adding date to your primary key like you have in your foreign key.

Jared