cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
florindaniel96
Frequent Visitor

Power BI SUM Anomaly

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
jared_farber Resolver III
Resolver III

Re: Power BI SUM Anomaly

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

 

Jared

View solution in original post

6 REPLIES 6
Highlighted
jared_farber Resolver III
Resolver III

Re: Power BI SUM Anomaly

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

 

Jared

View solution in original post

Super User IV
Super User IV

Re: Power BI SUM Anomaly

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.

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


florindaniel96
Frequent Visitor

Re: Power BI SUM Anomaly

@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.

Super User IV
Super User IV

Re: Power BI SUM Anomaly

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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


florindaniel96
Frequent Visitor

Re: Power BI SUM Anomaly

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 ?

Super User IV
Super User IV

Re: Power BI SUM Anomaly

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.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors