Forum Discussion
Dynamic Format values
Dears
I would like to complete that format to include minus signs in front of negative values.
the expected result to be (-1.2m) ,(-12.5K)
Thanks,
DAX format I used:
mhablas426 , Try this measure
FormattedFuelQuantity =
VAR Quantity = 'Fuel Formulas'[Fuel Total Quantity]
RETURN
IF (
Quantity >= 1000000,
FORMAT(Quantity, "0,,.00 M"),
IF (
Quantity >= 1000,
FORMAT(Quantity, "0,.00 K"),
IF (
Quantity <= -1000000,
FORMAT(Quantity, "0,,.00 M"),
IF (
Quantity <= -1000,
FORMAT(Quantity, "0,.00 K"),
FORMAT(Quantity, "0.00")
)
)
)
)
5 Replies
- fooddCommunity Champion
Hello mhablas426, and thank you for sharing you question with members of the Community Forum who are supportive. Remember to adhere to the decorum of the Community Forum when asking a question.
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you. Proud to be a Super User!
- mhablas426Advocate II
Thanks, I have re-edited my message to be clear
- rajendraongole1Super User
Hi mhablas426 - Can you try the below measure to report the expected result with () for negative
Formatted Value =
VAR Value = [Samperiodlast year Variance]
RETURN
IF(
ABS(Value) >= 1000000,
IF(Value < 0,
"-" & FORMAT(ABS(Value) / 1000000, "0.0") & "m",
FORMAT(Value / 1000000, "0.0") & "m"
),
IF(
ABS(Value) >= 1000,
IF(Value < 0,
"-" & FORMAT(ABS(Value) / 1000, "0.0") & "K",
FORMAT(Value / 1000, "0.0") & "K"
),
FORMAT(Value, "0.00")
)
)Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!! - bhanu_gautamSuper User
mhablas426 , Try this measure
FormattedFuelQuantity =
VAR Quantity = 'Fuel Formulas'[Fuel Total Quantity]
RETURN
IF (
Quantity >= 1000000,
FORMAT(Quantity, "0,,.00 M"),
IF (
Quantity >= 1000,
FORMAT(Quantity, "0,.00 K"),
IF (
Quantity <= -1000000,
FORMAT(Quantity, "0,,.00 M"),
IF (
Quantity <= -1000,
FORMAT(Quantity, "0,.00 K"),
FORMAT(Quantity, "0.00")
)
)
)
) - mhablas426Advocate II
I got those errors, could you assist, please
Pic 1Pic 2