Very interesting topic Ruth !! I am working a lot with different currencies and always try to keep as much as possible in PQ. If required I can simply copy the query to Excel and have immediately a very flexible solution... copy.. change .. etc. Have a nice weekend.
Hi Ruth. I just did this and it eventually worked. I was getting incorrect results when the model is exactly the same. The difference was my measure read ... SUMX(Fact, SUM(Fact[Sales]) * [xRate]). I changed it to SUMX(Fact, [Sales Lcl] * [xRate]) and perfect. I don't quite get it because obviously [Sales Lcl] = SUM(Fact[Sales]). Anyway ... probably useful for others to know.
Ruth, I love it. As usual you did a very good video this week. Very useful and in fact his is the solution i need on my current project. Thanks once again
Ruth! wow! I am really impress, I loved it! Gracias campeona mundial por todo este año de compartir tu experiencia y tu tiempo. Ha significado muchísimo para mi! Happy new year and everything else! Byte!
Hi Ruth, fantastic video. I was wondering if you have came across a currency conversion variance analysis that requiere to convert total sales at budget rate, actual rate and previous month rate, where the two last options change every month. Thanks for your help.
Hey Ruth beautiful artifact! But I'm going to raise the stakes: how do you handle different input currencies with different output currencies? example: regional sales table. I worked it around with a not very elegant power query table, would love to see your ideas. Thanks! Sos una genia ;)
@@CurbalEN Hi Ruth, I am not sure if this is the same thing Ed was asking, but I have a multi currency list and I need to convert all to USD. I have 3 currencies, USD, MXN, & CAD in one list.
All your posts are amazing, btw recently i heard about your channel and started watching the videos. If you tell me how to download the date query it wil be really helpful.
Hello, Ruth. If you are to revisit any of your great videos as at August 2019, which one would you do differently utilising the new features released to Power BI from September to date, that would deliver more value? Thanks for everything.
What a great question! Let me look at the updates that have been released and get back to you with a video. One question, why at August 2019 and not the entire year?
Curbal, The entire year is just ok. I used August only as a cutoff point for any video released since Januay or even earlier. And I think great features were released between September and November. You could even wait for the next and last release for the year. Thanks for attending to my request.
A wonderful video as always. Your channel is my go-to option whenever I'm stuck with Power BI. Do you have any video on how to show currencies on Clustered column chart or line chart? The numbers are reflecting with currency symbol when I'm using a table. But, on changing it to Clustered chart, the chart isn't coming up. I want to use it in this DAX measure: KPI Values = SWITCH([SelectedKPI], 1, [Total Cost], 2, [Total Sales], 3, [Total Profits], 4, [Total Quantity]) Please help. Thanks a ton in advance.
@@CurbalEN one measure is created min(exchange factor), and this is getting multiplied with total sales, My question is why we are taking Minimum of exchange factor
@curbal Hi I want to get single value from webservice in measure without loading data in a table and apply slicer value to filter webservice record. Is that possible?If yes please tell how?
Hi Ruth, great video and exactly what I need. Unfortunately it's not happening though. When I click the Report Currency Slicer it removes amounts of those who are not using that currency (rather than converting them). By that I mean if I click an item whose reportcurrency is GBP the amount is the same (as expected), but if I click a different currency it hides the amounts used in the calculation disappear i.e. the reportcurrency amount and the converted value for this. Any ideas what it could be?
Question: when you are creating a bridge table, is there any benefit in converting a list to a table? I typically List.Distinct() then leave it as a list if no additional columns are needed. Thanks, Ruth! 😊
Hi.. This is very detailed and interesting video. Actually do you think, I have another scenario which you could help me with... How about I choose the Country in my slicer and based on the exchange rate has to be calculated which is in a seperate table and the data in fact tables should display the data with the currency values of each country. so if i choose US in country filter, then it should calculate the total sales in US dollars and calculate the exchange rate in the seperate table.
I have a list of claim totals that contains USD, MXN, & CAD and I need all in USD. I created the slicer and when I choose MXN it converts the USD to MXN as well as the USD to CAD when I choose it from the slicer...is there another way to do this?
It doesn't make much sense to do the same thing in Power Query.Not DAX Friday at all ;) More efficient it would be if some function would automatically generate those exchange rates without the need to import them, it was my first thought maybe some function allows to do it :) kind of CONVERT(currency1, currency2, date)
Thank you, your videos are underrated ;)
Hola Ruth, mil gracias por este (y todos los demas) videos que pones de DAX Fridays!! my ayudan muchisimo
Un placer oírlo :)
thank you so much. you solved my 2 days of mental stress. i had the same problem in my task.
Wonderful 2 hear!
Very interesting topic Ruth !! I am working a lot with different currencies and always try to keep as much as possible in PQ. If required I can simply copy the query to Excel and have immediately a very flexible solution... copy.. change .. etc. Have a nice weekend.
Same to you Victor and thanks for sharing!
/Ruth
Hi Ruth. I just did this and it eventually worked. I was getting incorrect results when the model is exactly the same. The difference was my measure read ... SUMX(Fact, SUM(Fact[Sales]) * [xRate]). I changed it to SUMX(Fact, [Sales Lcl] * [xRate]) and perfect. I don't quite get it because obviously [Sales Lcl] = SUM(Fact[Sales]). Anyway ... probably useful for others to know.
Let me do a video!
/Ruth
Very interesting demonstration on currency conversion. Nice one, Ruth...
So simple is beautiful!
/Ruth
Thank you, you saved my life !!!!🤩
🥳🥳
Ruth, I love it. As usual you did a very good video this week. Very useful and in fact his is the solution i need on my current project. Thanks once again
Perfect timing then and happy Friday!
/Ruth
Ruth! wow! I am really impress, I loved it! Gracias campeona mundial por todo este año de compartir tu experiencia y tu tiempo. Ha significado muchísimo para mi! Happy new year and everything else! Byte!
Gracias Bruno!! Felices fiestas y año nuevo y mas para el año que viene!! 🎄
/Ruth
Super Ruth!!!!
🎉🎉
/Ruth
Hi Ruth, fantastic video. I was wondering if you have came across a currency conversion variance analysis that requiere to convert total sales at budget rate, actual rate and previous month rate, where the two last options change every month. Thanks for your help.
Hey Ruth beautiful artifact! But I'm going to raise the stakes: how do you handle different input currencies with different output currencies? example: regional sales table. I worked it around with a not very elegant power query table, would love to see your ideas. Thanks! Sos una genia ;)
Mmmm, not sure I understand, as that is what, in my mind, I explain in the video? More details please?
/Ruth
@@CurbalEN Hi Ruth, I am not sure if this is the same thing Ed was asking, but I have a multi currency list and I need to convert all to USD. I have 3 currencies, USD, MXN, & CAD in one list.
All your posts are amazing, btw recently i heard about your channel and started watching the videos. If you tell me how to download the date query it wil be really helpful.
Go to curbal.com - resources - download center and grab it from the community downloads.
Hello, Ruth. If you are to revisit any of your great videos as at August 2019, which one would you do differently utilising the new features released to Power BI from September to date, that would deliver more value? Thanks for everything.
What a great question! Let me look at the updates that have been released and get back to you with a video. One question, why at August 2019 and not the entire year?
Curbal, The entire year is just ok. I used August only as a cutoff point for any video released since Januay or even earlier. And I think great features were released between September and November. You could even wait for the next and last release for the year. Thanks for attending to my request.
Hi Ruth, I have bigdata and sumx is slowing down my query, is there something else that can replace it.
A wonderful video as always. Your channel is my go-to option whenever I'm stuck with Power BI.
Do you have any video on how to show currencies on Clustered column chart or line chart? The numbers are reflecting with currency symbol when I'm using a table. But, on changing it to Clustered chart, the chart isn't coming up. I want to use it in this DAX measure:
KPI Values =
SWITCH([SelectedKPI],
1, [Total Cost],
2, [Total Sales],
3, [Total Profits],
4, [Total Quantity])
Please help. Thanks a ton in advance.
Hey Ruth! Why the minimum of exchange factor is getting multiplied.?
Pravin Upadhyay
Not sure what you mean, can you give more details?
/Ruth
@@CurbalEN one measure is created min(exchange factor), and this is getting multiplied with total sales,
My question is why we are taking Minimum of exchange factor
He used MIN, but he could have used MAX or any other function that returns the value for that specific day and currency.
/Ruth
@curbal Hi I want to get single value from webservice in measure without loading data in a table and apply slicer value to filter webservice record. Is that possible?If yes please tell how?
Hi Ruth, great video and exactly what I need. Unfortunately it's not happening though. When I click the Report Currency Slicer it removes amounts of those who are not using that currency (rather than converting them). By that I mean if I click an item whose reportcurrency is GBP the amount is the same (as expected), but if I click a different currency it hides the amounts used in the calculation disappear i.e. the reportcurrency amount and the converted value for this. Any ideas what it could be?
Question: when you are creating a bridge table, is there any benefit in converting a list to a table? I typically List.Distinct() then leave it as a list if no additional columns are needed. Thanks, Ruth! 😊
Great question, I am guessing that when it gets imported it gets converted to a table either way, but I am not sure.
Maybe somebody here knows?
/Ruth
How can use and apply the culture ? Please can u post the file and video
Hi.. This is very detailed and interesting video. Actually do you think, I have another scenario which you could help me with... How about I choose the Country in my slicer and based on the exchange rate has to be calculated which is in a seperate table and the data in fact tables should display the data with the currency values of each country. so if i choose US in country filter, then it should calculate the total sales in US dollars and calculate the exchange rate in the seperate table.
This works fine, but TrnExchangeRate does not show correct exchange rate if you add it to the table
Wow, thanks!!!!
I have a list of claim totals that contains USD, MXN, & CAD and I need all in USD. I created the slicer and when I choose MXN it converts the USD to MXN as well as the USD to CAD when I choose it from the slicer...is there another way to do this?
It doesn't make much sense to do the same thing in Power Query.Not DAX Friday at all ;) More efficient it would be if some function would automatically generate those exchange rates without the need to import them, it was my first thought maybe some function allows to do it :) kind of CONVERT(currency1, currency2, date)
😂😂 Let me know if you crack it!!
Happy Friday,
/Ruth
Great!!!
🎉🎉
/Ruth