Forum Discussion

mhablas426's avatar
mhablas426
Advocate II
2 years ago
Solved

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:

IF (
    [Samperiodlast year Variance] >= 1000000,
    "0,,.00 M",
    IF ( [Samperiodlast year Variance] >= 1000, "0,.00 K", "0.00" )
)


 

  • 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

  • foodd's avatar
    foodd
    Community 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!

  • 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!!

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