Forum Discussion

fogyisland's avatar
fogyisland
New Member
9 months ago
Solved

IS it a bug for dynamic format or something happen?

environment: PowerBI Desktop+User defined Function +dynamic Format.

 

I define a custom function to set format for Data,here is function code:

Define
Function
Pbi.increase =()=>
Var currentValue = SELECTEDMEASURE()/100
VAR formattedValue = FORMAT(ABS(currentValue)/100, "0.0%")  
RETURN
    SWITCH(TRUE(),
        currentValue > 0, "▲ " & formattedValue,
        currentValue < 0, "▼ " & formattedValue,
        "↔ " & formattedValue
    )

 

this function will set format of value  to ▼ value if value is minus. but i found that set dynamic content format that show minus  before  ▼ . is this  bug for this function?

and my question is why i need divide 100 two times?

Var currentValue = SELECTEDMEASURE()/100
VAR formattedValue = FORMAT(ABS(currentValue)/100
 
if i change formatValue to FORMAT(ABS(currentValue) , if the float value has two or more integer part,  it will return wrong value .

 


 

is it a bug for that?

 
 
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 months ago

    I was just writing a reply and see GrowthNatives has already replied 🙂

    Just to add:

    • You can positive/negative/zero values of the underlying measure using the format string syntax. See example here on SQLBI.
    • You could write your function as below or simply set a constant format string since the three possible formats are handled in the string itself:
    ()=>
"▲ 0.0%;▼ 0.0%;↔ 0.0%"

     

  • danextian's avatar
    danextian
    9 months ago

    You're supposed to be returning a format string and not a formatted value. Also if you use a condition to append a triangle, that is being appended to the current value (which could be positie or negative). You could have simply used as a format sring: "▲0.00%;▼0.00%;↔0.00%"

5 Replies

  • GrowthNatives's avatar
    GrowthNatives
    Icon for Super User rankSuper User
    9 months ago

    Hi fogyisland , 
    This is not a bug. What you are seeing is the expected behavior of Dynamic Format Strings combined with percentage semantics in Power BI. The confusion comes from mixing value scaling, FORMAT(), and model-level percentage formatting.

    Your measure is already negative, and Power BI applies the numeric sign before the dynamic format string is rendered. 

    By dividing SELECTEDMEASURE() by 100 in your dynamic format logic, you rescale an already-scaled value.

    Dividing again inside FORMAT(..., "0.0%") causes double scaling, which leads to incorrect results.

    The correct way to do this : Never rescale percentage values inside dynamic format strings

    Pbi.Increase =
VAR v = SELECTEDMEASURE()
RETURN
SWITCH(
    TRUE(),
    v > 0, "▲ 0.0%",
    v < 0, "▼ 0.0%",
    "↔ 0.0%"
)

     

    If you want to REMOVE the minus sign (only arrows)

    YOY % (Unsigned) =
ABS ( [YOY %] )

     

    ⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
    💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
    🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    9 months ago

    I was just writing a reply and see GrowthNatives has already replied 🙂

    Just to add:

    • You can positive/negative/zero values of the underlying measure using the format string syntax. See example here on SQLBI.
    • You could write your function as below or simply set a constant format string since the three possible formats are handled in the string itself:
    ()=>
"▲ 0.0%;▼ 0.0%;↔ 0.0%"

     

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    9 months ago

    You're supposed to be returning a format string and not a formatted value. Also if you use a condition to append a triangle, that is being appended to the current value (which could be positie or negative). You could have simply used as a format sring: "▲0.00%;▼0.00%;↔0.00%"

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 months ago

    Hi fogyisland ,

    Just checking in to see if you query is resolved and if any responses were helpful.
    Otherwise, feel free to reach out for further assistance.

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 months ago

    Hi fogyisland ,
    Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
    Thank you.