Can I cast a currency Text string to currency type to use as value in heatmap?

[GrahamB]

I have a need to convert or cast the string result of a DAX query (in the format "£#,###") into its CURRENCY equivalent so that I can use it as a value in a heatmap / treemap.  Any help to resolve this issue would be deepy appreciated.  

[GrahamB]

I'm dynamically changing currency depending on a table value and this returns a string I've formatted appropriately, however I also want to use this result as a value in a heatmap / treemap and I can't find a way to convert it back to currency to enable this.  Any help greatly appreciated (obviously in this context changing the formatting options in the modelling ribbon would not be a useful approach).

[v-huizhn-msft]

Hi @GrahamB,

I try to reproduce you scenario and get expected result.

The first screenshot is currency values, I transfer it to $ use the formula and it returns text result shown in screenshot, please see the highlighted in yellow background.

 

Then click the Modeling-> Data Type. Transfer it to Decimal Number highlighted in red line successfully.

Could you please share your DAX query returning string result and snapshot for analysis?

Best Regards,
Angelia

[GrahamB]

Many thanks for your reply Angelia.  The specific difficulty involves this DAX query:

DynamicRevenue = if(HASONEVALUE(dynamic_currency_test[CurrencyType]),
SWITCH (Values(dynamic_currency_test[CurrencyType]),
"Pound", FORMAT([Value], "£###,###"),
"Dollar", FORMAT([Value], "$###,###"),
"Yuan", FORMAT([Value], "¥###,###"),
"Euro", FORMAT([Value], "€###,###"),
"Peso", FORMAT([Value], "₱###,###"),
"Won", FORMAT([Value], "₩###,###"),
"Zlotty", FORMAT([Value], "###,###zł"),
FORMAT([Value], "#.##")
)
)

This information is used elsewhere in the desktop display I have designed; but becasue the purpose is to dynamically change currency based on input values from a table column I also want to display the same value (and use the value for calculation) in a treemap.  The treemap area is based on the value (which I am currently employing without a currency value - but this is not ideal in context), however the important point is that I want to dynamically change the currency symbol (to prepend or append) to the currency value.using this dynamic method.  

I hope this helps to illustrate the issue which is caused by dynamic updates to the reports I have created for multiple users (it is important that these users can all use one report because it is served via a website but I cannot rely on their localisation picking up the appropriate currencies - as the reporting currency they deal with may not be the same as the jurisdiction in which they are working).

[v-huizhn-msft]

Hi @GrahamB,

You'd better create a slicer to filter the currency, when you select the "Pound", you will get the result £###,###. The [Value] used in DynamicRevenue measure my be measure, rather than column, I try to reproduce the scenario using the following screenshot and get the expected result.



First create two measure s using the following formula.

Total-Value = SUM(Table6[currency])

DynamicRevenue = if(HASONEVALUE('Currency Test'[CurrencyType]),
SWITCH (Values('Currency Test'[CurrencyType]),
"Pound", FORMAT(Table6[Total-Value], "£###,###"),
"Dollar", FORMAT(Table6[Total-Value], "$###,###"),
"Yuan", FORMAT(Table6[Total-Value], "¥###,###"),
"Euro", FORMAT(Table6[Total-Value], "€###,###"),
"Peso", FORMAT(Table6[Total-Value], "₱###,###"),
"Won", FORMAT(Table6[Total-Value], "₩###,###"),
"Zlotty", FORMAT(Table6[Total-Value], "###,###zł"),
FORMAT(Table6[Total-Value], "#.##")
)
)

Then create a slicer including the currency type.

Finally, create a table, select the date, "Total-Value" as values. Please see the following screenshot, when you select the Pound, the result shown in £###, which when you select "Dollar", the result shown in $###.




If you have any other issue, please feel free to ask.

Best Regards,
Angelia

[GrahamB]

Once again many thanks for your reply.  Unfortunately it's not really getting me any further forward because I can't rely on a slicer in this context (the reason being; that the same value in a different currecny is a differnt amount, therefore the point of attempting to make this process dynamic is to avoid the possibility of users getting the 'wrong' currency in the report, providing the currency data in a table appears the only correct way to enforce this necessary restriction).  

My constraints may mean that this is impossible in Power BI reporting, but they include:

The currency value must come from the report table

The currency value must be applied dynamically without user interaction

The currency valuies must be able to be used in a heat map / tre map as values (i.e. as currency)

I already have the dynamic currency applied in table format as text elsewhere and there is no issue with this.  Your solution (for which I thank you very much) is unfortunately not applicable in the particular case I have to hand, although it is a super way of enabling totals to colums which I might apply as a cross check to the data set I'm currently working with.

I would be more than happy to provide more information on the particulars if they will be beneficial.

Kind regards

GrahamB