Format as currency within a measure in bar chart

[phuonganhtdinh]

I have a bar chart with all department names on the Y-axis. I've created a table with names and IDs of 2 different KPIs (Sales and Number of employees) so that users can select either Sales or No. of employees from a KPI slicer and the chart would display accordingly, one at a time. The Values measure used in the chart goes like this : SWITCH(VALUE([KPI ID Selected]), 1 , [Count of employees measure], 2 , [Sales measure], 0 ). It is showing both No. of employees and Sales in whole number format. 

How do I format the sales measure only in this Values measure for the chart to display Sales in VND? I've tried CURRENCY() and it didn't change anything. Thanks!

[v-lili6-msft]

hi  @phuonganhtdinh 

For your case, the best way is use bookmark function to get it, here is a blog, you could refer to it:

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

Regards,

Lin

[amitchandak]

@phuonganhtdinh , you can pre-format and use but I doubt will change it into text so might not work

https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings

[phuonganhtdinh]

@amitchandak Right, it turned into text. Thanks anyway!

[parry2k]

@phuonganhtdinh this can be done using calculation groups but you have to use Tabular Editor to achieve the same. Read this post here.

There is an idea on ideas forum, please vote when you get a moment so that this feature is directly available in Power BI.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[phuonganhtdinh]

I've read that post, unfortunately this isn't applicable to our setup at the moment. Thanks for the suggestion, I've never heard of calculation groups before!

[parry2k]

@phuonganhtdinh  anyhow, glad you learned something new, stay on top of it, can be very useful.

[edhans]

You'd need to return both of them as Text and use the FORMAT() function in your SWITCH statement. I don't know of a way to conditionally format number formats in Power BI. You can do colors, icons, bars, and more, but not number formats.

[phuonganhtdinh]

@edhans Thanks. Wouldn't that break the bar chart though? As it only accepts values.

[edhans]

Yup, probably so.

What I would do is:

1. Create two tables, one for dollars and one for employee count.
2. Create a two buttons, one for employee count, one for sales dollars
3. Create a bookmark that hides the "employee count" button and sales dollar chart
4. Create a bookmark that hides the "sales dollars" button and employee count chart
5. connect the #3 bookmark above to the Sales dollars button'
6. connect the #4 bookmark above to the employee count button

Let them toggle that way vs a slicer. You'll need to split your employee count and sales dollars into separate measures and format accordingly.