Formatting issues with currency in Power BI

Hi,

I formatted a column in my dataset as currency (Sales values) and I use it in the measure below:

RunningNettoWeek = CALCULATE(SUM(Sales[NETTOPREMIE]);FILTER(ALL(Sales[Week]);Sales[Week] <= MAX(Sales[Week])))

But whenever I run the measure, the values lose their formatting. I tried wrapping it with CALCULATE() function in FORMAT(), but the values ended up not showing in my charts. How can I retain the formatting in this scenario?

This is quite simple really

It can be done by simply going to the Data Modeling area, and then click on your measure and click on the $ icon to format the measure as currency.

You don’t need to use FORMAT for simple things like this. Just utilize the formatting feature. You only have to format it once this way and then you’re done.

Sam

I’m running into this issue as well and I have formatted the column as currency but when displaying the visuals they only come out as numbers with M, or Bn to denote any kind value.

Any idea why this is happening?

Thanks
D

Hello @DDewbre, this sounds like a visual display issue - you need to check the formatting pane for that visual in the visualizations panel.

  1. Select the visual that is showing the value you want to correct
  2. Find the area (in formatting section image ) that is dealing with Data lables or Data values (what it is called depends on what type of visual you are using)

next you need to look for the “Display Units” drop-down, you can change this from Auto (default setting) to show the value in a particular unit type (such as “M” or “Bn”)
image
Selecting “None” will show the value as you have defined it.

Hi Heather! Yepper, I’ve done that as well…I think it just doesn’t like me! LOL! I’ve selected each one with no luck…still no $. Currency Issue v1

Change Display units from “Auto” to “None”, that should fix it.
NOTE: with a number going into the billions, you may need to expand that card visual.

It didn’t, it just showed the full value…but no $ sign. This is why I’m confuzzled because this is the first time I’ve had this issue…it’s like an update changed something. Actually is doesn’t show in any of the visuals…including charts.

Very strange, I haven’t run into anything like this yet.
I haven’t done any design work since late in January, so it’s always possible that an update changed things.

Is it possible for you to post a condensed version of your model?

Let me see if I can skinny it down and post

Interesting, I went back to an older report and the $ is showing on those visuals. Let me do a comparison and see if I did something different on the old one…I doubt it…but maybe.

So it looks like it might actually be a field issue…maybe. I’m looking at one of my new reports and I have half of them showing with $ signs and half not. Very interesting.

Forgive me if I’m being too obvious here - but how is the measure formatted?
Not the column in the data model, but the measure?

image

I have seen where the column in the model must be formatted as currency to work.

Guy

OMGarsh!! How simple…you where not be too obvious…I’m obviously a tad blind Heather! :smile: That fixed the issue…sometimes I wonder about me! LOL!

Guy, thank you so much for responding as well! Yes, I did do this, but was missing that critical step of formatting the measure.

:slight_smile: Hey, it’s Friday, I think we all need to allow for a bit of what I call ‘brain fuzz’ at times.
I’m glad the solution was a simple one, believe me, I’ve had plenty of times that I was SURE I’d done everything right, but there was one simple piece that was missed.

And most PowerBI development has a LOT of places where a small detail makes all the difference.
Have a good weekend!

1 Like

I hear you!! Thanks again and have a great weekend!!