Formatting Currency with and without decimals in IF Formula

[astanfo]

I have 3 tables that I have connected through relationships. One table has the location struction, another has the expenses for each loaction, and another has the widgets that each structure. I have a measure that takes the expenses and divides the expenses by the sumx(relatedtable(widget), widget.widget). I cannot share the data due to company privacy. I am trying to figure out how to take the Cost Per Widget and if the measure is greater than 1,000 or less than -1,000 then display the currency with zero decimal places. If the number is less than 1,000 or greater than -1,000, then display the currency with 2 decimal places. I DO NOT WANT TO HAVE THE VALUE SHOW AS TEXT because then I cannot use the number in a graph or chart. Does anyone know what DAX formula would be best for the measure?? When setting up the measure I have tried the below formula but it displays the value with trailing zeros that I do not need. Also, I have this measure set as Decimal for the data type and General Currency $ for the format.

FORMAT = IF( OR([Cost_Per_Widget]>1000, [Cost_Per_Widget]<-1000), INT([Cost_Per_Widget]), convert(FIXED([Cost_Per_Widget], 2), CURRENCY))

Return Value Examples: $8.130000000000001, $75.26000000000000005, $425.269999999999998

I would use the Dollar () function, that is in excel, but unfortunately there is no such function in Power BI.

Thank you

[Melinda_Gates]

try the below:

"$" & FIXED([Cost_Per_Widget], 2)

[ashwaghosh]

I am trying to do the same task, but I realised that display front end and background formats are two different things. Whatever operations we are trying to apply will be on decimals numbers, no need to change the data type for background operation. Once we apply the formula and obtained the desired result we can use from end options under column tools to format it using currency. 

[Greg_Deckler]

So, this will get you everything except the $

Column = IF( OR([Value]>1000, [Value]<-1000), FIXED([Value],0), FIXED([Value],2))

[Greg_Deckler]

Now that I am testing this, I think there is a bug in CURRENCY. If I wrap my FIXED statements in CURRENCY, it zeros out the decimals. This doesn't seem like what the documentation says:

From: https://docs.microsoft.com/en-us/dax/currency-function-dax

The CURRENCY function rounds up the 5th significant decimal, in value, to return the 4th decimal digit; rounding up occurs if the 5th significant decimal is equal or larger than 5. For example, if value is 3.6666666666666 then converting to currency returns $3.6667; however, if value is 3.0123456789 then converting to currency returns $3.0123.

That is not the behavior I am experiencing. However, that being said, I wonder if this would still fall afoul of the Formatting specified in the ribbon for a measure or column which you have to specify 0 or 2 decimal places and either way, the result you get will be not what you want.

[Tad17]

Hey @astanfo 

You just need to wrap the portion of your formula that is giving you the solution in a FORMAT function

FORMAT DAX: https://docs.microsoft.com/en-us/dax/format-function-dax

Then use a custom format for each of your possible outcomes either giving it decimals or not:

Custom Numeric Formats for FORMAT DAX: https://docs.microsoft.com/en-us/dax/custom-numeric-formats-for-the-format-function

If this helps please kudo.

If this solves your problem please accept it as a solution.

[astanfo]

What would I wrap the formula in? Currency()? If I do that, then it will still give me multiple decimal places.

https://docs.microsoft.com/en-us/dax/currency-function-dax

[Tad17]

You'll need something along the lines of:

FORMAT = IF( OR([Cost_Per_Widget]>1000, [Cost_Per_Widget]<-1000), FORMAT([Cost_Per_Widget], "$#,##0;($#,##0)"), FORMAT([Cost_Per_Widget], "$#,##0.00;($#,##0.00)")

I'm not sure if that is quite right, but it should be close.

Also check out this article: https://radacad.com/custom-format-fields-and-measures-in-power-bi

I like using FIXED, but it doesn't give you the "$"

[Greg_Deckler]

@Tad17  but using FORMAT like that, won't that just convert it to Text?

[Tad17]

@Greg_Deckler Yeah I realized that, but not sure how to get the conditional decimal place otherwise. Can @astanfo create a calculated column using your method and then just change it to currency format in the modelling tab?

[astanfo]

@Tad17 I cannot create a calculated column. I simplified the explanation for my data in order to see if there is a solution for a measure. I am using calculate(), crossfilter(), and switch() within my measures so it needs to stay a measure. Any other recommendations? Do you know if the Dollar() command will be created in the future?

Also, I need this all in one measure. So, when I filter on $ then the number will show as a currency with no decimal places. When I filter on $ per widget then the number will be displayed with two decimal places. It's important for me to have a filter for this instead of showing two columns, because it saves space on the page and one person who is viewing the page might want to look at $ while another person may only want to look at $ per widget.

[astanfo]

Hey everyone, both Fixed() and Format() convert the number to a text. Therefore, the number will show up fine in a matrix table but when put into a line chart or another chart the measure will not work.

[Greg_Deckler]

So I tried an idea I had to use the Custom Formatting (for display only) technique used in Chelsie Eiden's Duration. https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

I created 2 measures and had their display formats as $#,### and $#,###.##. These worked great. Leaves them as numbers but displays the right format. My thought then was to write a third measure that selected between these two measures. This also works but runs afoul of the Formatting ribbon decimal places as I had feared. PBIX is attached.

[astanfo]

@Greg_Deckler Hi Greg, can you explain more in detail your sugested solution?

If I use one measure to display the format as $#,##0 and another measure to display the format as $#,##0.#0 then I will still need to use a measure to select these measures. Are you saying the measure (call it SELECT) that selects the two formatting measures will format the number based on what the format is in the formatting measures instead of the format of the SELECT measure?

[Greg_Deckler]

Right, so the problem with finding a resolution to this solution is that since it all comes down to a single measure, the Formatting in the ribbon will be the final arbiter of how many decimals are displayed. This can only be set to a single thing, either 0 decimals or 2 decimals. Thus, I cannot see a solution at present for what you are trying to do other than to convert it to text for display purposes, which you don't want to do. When I am referring to the ribbon, I mean this:

 

[astanfo]

@Greg_Deckler Any new development/solution with this?

[astanfo]

Okay, it's a bummer there is no formula that specifies how may decimal places you want in a number/currency.

[smpa01]

@astanfoyou might want to give this a go through Tabular Editor. Tabular Editor has a C# advanced scripting option for measures. It is worth trying a conditional C# for custom formatting. I know Tabuular editor has a lot of functionalities for DAX measures and bulf formatting and things like that.

Resources - https://docs.tabulareditor.com/Useful-script-snippets.html

                   https://tabulareditor.com/

You migtht want to initiate tabular editor through external tools in PBI.

 I am not 100% sure it would work out though.

[astanfo]

@smpa01  From a brief look at tabular editor, it looks like I would come across the same problem.

Are you aware of a way in Tabular Editor to make a measure the currency format (Not Text) but change the decimal places depending on the size of the number ( If OR(>1000,<-1000) then make currency format with 0 decimal places else make currency format with 2 decimal places)?

[smpa01]

@astanfodid take a look at this and my approach was same was @Greg_Deckler with FIXED, but can't wrap it inside CURRENCY.

if you do find a solution to this DAX/Tab. editor or something else please don't forget to tag me in that solution.

[romgut]

it's really pitty.

The cards are formatting the numbers quite nice - if the number is too long it cuts it and writes tsd or mil and you don't have to play with the format in order to get it fit into the card.

But if it's a monetary value, Business wan'ts to see a currency sign.

Adding the currency sign is possible via format as "Currency" but the the number is like 13'54... and you need to adjust the text size of the card.

I want to have both - currency sign and smart rounding 🙂