cancel
Showing results for
Did you mean:

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

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.

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!

2 ACCEPTED SOLUTIONS
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"))))

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

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

Super User

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

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

7 REPLIES 7
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"))))

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

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

Frequent Visitor

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

Super User

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

Frequent Visitor

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

Super User

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

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

Frequent Visitor

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

Super User

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

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

#### 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