cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
JohnCarl45
Frequent Visitor

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

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

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

@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)

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

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

Glad to help 🙂

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors