Switch between currencies and show it in "Analyse in Excel": A table of multiple values ...

[Hi69]

Hi, I searched for a combination of my topics but was not able to find the solution for my problem.

I have an online connection which I only update frequently where I get several values (e.g. Net sales, different Cost categories) and calculate values on top (e.g. Gross Margin, price and cost per unit).

The values so far only in one base currency.

Beside the visuals in the Dashboards also the information can be downloaded for "Analyse in Excel".

So far I created a currency table with rates including the base currency with a measure called "Selected currency":

Selected currency = VALUES('Currency'[Currency])

and a slicer to choose the currency.

I tested a switch formula which worked very well in the visuals:

Net Sales currency = SWITCH(TRUE(),

[Selected currency]="EUR", [Net Sales SEK]*VALUES('Currency'[Rate]),

[Selected currency]="USD", [Net Sales SEK]*VALUES('Currency'[Rate]),

[Selected currency]="SEK", [Net Sales SEK]*VALUES('Currency'[Rate]))

but unfortunately when it comes to "Analyse in Excel" I get the message mentioned in the header:

A table of multiple values was supplied where a single value was expected

What needs to be done to enable the Excel analysis correctly?

Thanks a lot for your help

[Hi69]

@amitchandak 

Thank you very much for your reply.

Unfortunately it is not working - both are working in the Dashboards (depending on relationship or not) but non of them are finally working in the "Analyse in Excel" file.

[v-xinruzhu-msft · Accepted solution]

Hi  @Hi69 

You can try the following solution.

Selected currency = SELECTEDVALUE('Currency'[Currency])

Then modify the Net Sales currency measure:

Net Sales currency = SWITCH(TRUE(),

[Selected currency]="EUR", [Net Sales SEK]*SELECTEDVALUE('Currency'[Rate]),

[Selected currency]="USD", [Net Sales SEK]*SELECTEDVALUE('Currency'[Rate]),

[Selected currency]="SEK", [Net Sales SEK]*SELECTEDVALUE('Currency'[Rate]))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hi69]

Yes, yes, yes 😄 - that is working.

Thank you so much!

[amitchandak]

@Hi69 , you have to use max/min or related if the is 1-m realtion

Net Sales currency = SWITCH(TRUE(),
[Selected currency]="EUR", [Net Sales SEK]*Max('Currency'[Rate]),
[Selected currency]="USD", [Net Sales SEK]*Max('Currency'[Rate]),
[Selected currency]="SEK", [Net Sales SEK]*Max('Currency'[Rate]))

Net Sales currency = SWITCH(TRUE(),
[Selected currency]="EUR", Sumx( Table, [Net Sales SEK]*realted('Currency'[Rate])),
[Selected currency]="USD", Sumx( Table, [Net Sales SEK]*realted('Currency'[Rate])),
[Selected currency]="SEK", Sumx( Table, [Net Sales SEK]*realted('Currency'[Rate])))

refer few more options

currency
https://www.youtube.com/watch?v=4dosxfNxR6M
https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
https://radacad.com/currency-converter-power-bi-function-part-1
https://www.youtube.com/watch?v=Q4hF4CwJsFEhttps://radacad.com/currency-exchange-rate-conversion-in-...
https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-us...
https://blog.beyondimpactllc.com/blog/building-a-dynamic-currency-converter-with-power-bi