How dynamically change the currency symbol for summarisation fields?

[alexkaranda]

We are facing a challenge applying dynamically the right currency symbol based on the currency field from the table.

Using API, we are preparing  the following table with information about purchased products, where the key fields are:

• created_at,
• name,
• row_total,
• and currency. 

Where row_total is actually the purchased amount per order, it is decimal with enabled summarization. 

The goal is to build a summarized report with the top 10 purchased products based on the row_total 

Challenges:

1. We can't use configuration settings to setup manually the right currency formatting. 
   1. currency might be changing depending on the data source
   2. this dashboard is not for developers, so we don't want to ask the sales representative each time to change currency manually
2. FORMAT function is generating an error for Summarizing field
   1. We've created a new measure for formatting 

   2. currencyFormat = 
      var cur = CALCULATE(MIN(OrderItem[currency]))
      return switch(cur, "GBP","£#,##0.#", "EUR", "€#,##0.#", "$#,##0.#");

   3. Tried to apply the format function  

      FORMATED_ROW_TOTAL = FORMAT(row_total,currencyFormat )

   4. If it is just a regular table view, it shows nicely formatted values.
   5. But the problem, these are string values!
   6. And once we try to use it for the summary report it fails with the error "cannot convert string to integer"

In our case, the ideal scenario is to modify the row_total field "custom format" settings using DAX but seems it impossible. 

Or, using Parameters to define the current currency, which also seems also not possible.

One of the ideas was to prepare a separate measured table with already summarized and grouped data per product. 

But it sounds crazy and complex.  

Appreciate any reasonable ideas. 

[DataInsights · Accepted solution]

@alexkaranda,

You could try using a calculation group (see article below). A calculation group gives you more control over formatting and doesn't convert the data type to text.

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/ 

[alexkaranda]

This solution works perfectly for my case.

Not the ideal, but completely different level working  with Power BI

Some useful link who wants to learn more: 

https://learn.microsoft.com/en-us/training/modules/create-calculation-groups/5-lab