topic Re: Dynamic Format values in DAX Commands and Tips

Dynamic Format values

mhablas426 — Tue, 09 Jul 2024 11:48:51 GMT

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

)

Re: Dynamic Format values

foodd — Sun, 07 Jul 2024 11:20:04 GMT

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!

Re: Dynamic Format values

mhablas426 — Sun, 07 Jul 2024 11:44:11 GMT

Thanks, I have re-edited my message to be clear

Re: Dynamic Format values

rajendraongole1 — Sun, 07 Jul 2024 13:32:39 GMT

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

Re: Dynamic Format values

mhablas426 — Tue, 09 Jul 2024 12:19:13 GMT

I got those errors, could you assist, please

Re: Dynamic Format values

bhanu_gautam — Thu, 11 Jul 2024 06:23:10 GMT

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