Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JohnCarl45
Helper I
Helper I

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

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.

JohnCarl45_0-1691630896373.png

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!

JohnCarl45_1-1691631028233.png

 

2 ACCEPTED SOLUTIONS
Ritaf1983
Super User
Super User

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"))))
Ritaf1983_0-1691635807443.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

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)
)
Ritaf1983_0-1691656608473.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

7 REPLIES 7
Ritaf1983
Super User
Super User

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"))))
Ritaf1983_0-1691635807443.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

 

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

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)
)
Ritaf1983_0-1691656608473.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

Glad to help 🙂

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.