HOW TO CHANGE THE VALUE FROM -0.2M TO 152.2K USING DAX

[JohnCarl45]

Hi guys, good day to all. I have a problem on the column. I tried to formulate it using Dax Formulas, but it cannot round off to thousand, and the ending is that it shows -0.2M but not -152.2K.

The formula I use is this. My question is  How to change the the value -0.2m to 152.2K? It is a big help, guys, if you give any tips on my problem. Thank you all!

[Ritaf1983 · Accepted solution]

Hi @JohnCarl45 
Just add the conditions for formatting negative numbers.
For example :

Test =

var total = AVERAGE ('Table'[Value])

return

if (total>-1000 && total<0,format(total,"0.0"),

if (total<=-1000 &&total>-1000000,format(total/1000,"0.0K"),

if (total <-999999,format(total/1000000,"0.0M"))))

you can modify it as you need to be a part of your formula.
Link to a sample 

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

[Ritaf1983 · Accepted solution]

Hi @JohnCarl45 
Just add the conditions for formatting negative numbers.
For example :

Test =

var total = AVERAGE ('Table'[Value])

return

if (total>-1000 && total<0,format(total,"0.0"),

if (total<=-1000 &&total>-1000000,format(total/1000,"0.0K"),

if (total <-999999,format(total/1000000,"0.0M"))))

you can modify it as you need to be a part of your formula.
Link to a sample 

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

[JohnCarl45]

@Ritaf1983  Hi, Good day. Can you show me how to apply that to my formula or add it up? Thank you.

[Ritaf1983]

Yes. share your PBIX or at least a code on format that i can edit ( not screenshot)

[JohnCarl45]

Sure The Code of the dax formula that i used

Net income custom # =

Var TotalSale = SUM('Transpose version of IS Perstor'[NET INCOME])

Var decimals = "0.0"

RETURN

SWITCH( TRUE() ,

TotalSale > 100000000 , FORMAT(TotalSale / 100000000, decimals & "M"),

TotalSale > 1000000 , FORMAT(TotalSale / 1000000, decimals & "M"),

TotalSale > 1000 , FORMAT(TotalSale / 1000, decimals & "K"),

TotalSale > 1 , FORMAT(TotalSale / 1, decimals & ""),

TotalSale < 1000000 , FORMAT(TotalSale / 1000000, decimals & "M"),

TotalSale < 100000 , FORMAT(TotalSale / 100000, decimals & "K"),

TotalSale < 1000 , FORMAT(TotalSale / 1000, decimals & "K"),

FORMAT(TotalSale , decimals)

)

Net Income Column

-477,761.5

-1,180,021,.6

-739,495.6

-912,979.6

-152,167.2

450,036.6

That's the column of the table on the code i only used on column, but the other side is that column is filter by categories, but in the total of that whole column is up to million, that's why they have ten millions and millions on my formula, but the point is those negative numbers i can't transform them into thousands that's why

[Ritaf1983 · Accepted solution]

Hi @JohnCarl45  :

This is the basic formula, I didn't understand what did you try to do with tens of millions so just modified it in the same logic.

Net income custom # =

Var TotalSale = SUM('Table'[Net Income Column])

Var decimals = "0.0"

RETURN

SWITCH( TRUE() ,

TotalSale < -999999 , FORMAT(TotalSale / 1000000, decimals & "M"),

TotalSale <-999 && TotalSale >-1000000 , FORMAT(TotalSale / 1000, decimals & "K"),

TotalSale < -1 && TotalSale > -1000, FORMAT(TotalSale , decimals & ""),

TotalSale > 0 && TotalSale <1000  , FORMAT(TotalSale / 1, decimals & ""),

TotalSale > 999 && TotalSale < 1000000 , FORMAT(TotalSale / 1000, decimals & "K"),

TotalSale > 999999 , FORMAT(TotalSale / 1000000, decimals & "M"),

FORMAT(TotalSale , decimals)

)

The linked file has also been updated: Link to a sample 

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

[JohnCarl45]

 I figured it out, ma'am. Thank you so much for the help!

[Ritaf1983]

Glad to help 🙂