How can I add dynamic currency prefix in sales field to show it in a bar chart ?

[Femcold]

I created a measure that adds a currency prefix to Revenue and this works perfectly for a table but it turns of the data lables for a bar chart, I read on blogs that the format statement in my dax formula is return a text value. How do i work around this?

Revenue =

var _revenue = SUM('Invoice lines'[Customer invoice line amount - accounting currency])

return

FORMAT(_revenue,

    SWITCH(

        SELECTEDVALUE('Entities'[ID],"20CA"),

        "20CA","$#,##0.##",

        "30US","$#,##0.##"

        )

)

[v-yanjiang-msft]

Hi @Femcold ,

Does my solution work for you? If so, would you mind accept it as solution? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @Femcold ,

I think it's because the function FORMAT will return a text data, which cannot be displayed in the Y-axis of bar chart. The Y-axis of bar chart represent the size of value, it should be a number.

Best Regards,
Community Support Team _ kalyj

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

[Femcold]

@v-yanjiang-msft , what would be a possible remedy in this scenerio

[v-yanjiang-msft]

Hi @Femcold ,

I think you can directly change the data format to Currency.

You can also click the drop-down option besides the $ symbol, there're all currency symbols in it.

Best Regards,
Community Support Team _ kalyj

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

[Femcold]

hi @v-yanjiang-msft, thanks for your reply but i have three currencies that i am switching based on a slicer,

FORMAT(_sales,
    SWITCH(
        SELECTEDVALUE('Currency'[Currency Format],"USD"),
        "USD","$#,##0.##",
        "GBP","£#,##0.##",
        "Euro","€#,##0.##"
        )
)

The format automatically turns the Measure to a text and i cant use it in a chart

[v-yanjiang-msft · Accepted solution]

Hi @Femcold ,

As a workaroud, you can use bookmark instead of slicer, here's the detailed steps:

1.Create measures for the result, and set each measure a seperate currency format.

USD = MAX('Table'[Sales])

GBP = MAX('Table'[Sales])

2.Create two charts with two measures seperately, and put them overlapping. Then create bookmarks with only one chart show and others hided. Now you can directly switch the bookmark to display charts with diffrent currency.

3.You can still create buttons, turn on the Action option and select corresponding bookmark.

Now, you can change the data label by Ctrl+click the corresponding button.

Reference:Create report bookmarks in Power BI to share insights and build stories - Power BI | Microsoft Learn

Create buttons in Power BI reports - Power BI | Microsoft Learn

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Femcold]

As you can see in the image the switch chnages the currency but removes the label on the chart