Conditionnal Number Format

[Mahieddine]

Good evening,

I'm working on a dynamic column chart which displays a specific metric depending on the option selected in a separate slicer. Here is the measure used in the Y-Axis to get this result : 

Y - Axis = VAR SelectedMeasure = SELECTEDVALUE('Measure Table'[Y-Axis]) RETURN SWITCH ( SelectedMeasure, "EV / EBITDA LTM", [EV / EBITDA (LTM)], "EV / EBITDA NTM", [EV / EBITDA (NTM)], "EV / Industry Metric", [EV / Industry Metric], "EV", [EV (in M$)], "Discount Rate", [Discount Rate (%)], BLANK () ).

The dynamic feature works fine mostly apart from one issue : The number format isn't ajusted based on the metric observed. I would like to get a chart with values in percentage when the metric chosen in the Discount Rate and with decimal values for other metrics ( see picture below, Y-Axis displayed a value of 0.12, the result achieve is 12.00% as shown by Y-Axis Spe)

 

The closer I got to achieving the result wanted was through the measure Y-Axis Spe and Formatted Y - Axis. The measures works on Tables but for some reason, it doesn't work on column charts. 

How can I get a measure that can adjust the number format when a specific metric is selected in the slicer (in this case, the "discount rate") and that works on graph ?

I would really appreciate some help if anyone has a solution in mind.

Thank you very much!!

[parry2k · Accepted solution]

@Mahieddine I guess selectedvalue  is the issue, read this post to get the selected value.

[parry2k · Accepted solution]

@Mahieddine this should be your measure (in my opinion)

Dynamic Title - progression annuelle =
VAR SelectedValue1 =
    SELECTCOLUMNS (
        SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ),
        Parameter[Parameter]
    )
VAR SelectedValue2 =
    SELECTEDVALUE ( 'Comparables Database - Power BI Dashboard'[Sector] )
RETURN
    SWITCH (
        TRUE (),
        SelectedValue1 = "EV / Industry Metric",
            SWITCH (
                TRUE (),
                SelectedValue2 = "Infrastructure", "EV / MW",
                SelectedValue2 = "Agriculture", "EV / Hectare",
                "EV / Industry Metric"
            ),
        SelectedValue1 = "Discount Rate", "Discount Rate",
        SelectedValue1 = "EV / EBITDA LTM", "EV / EBITDA LTM",
        SelectedValue1 = "EV / EBITDA NTM", "EV / EBITDA NTM",
        SelectedValue1 = "EV / Revenue", "EV / Revenue",
        SelectedValue1 = "EV", "EV (in M$)"
    )

[Mahieddine]

Thank you very much for your help, it work perfectly!

[parry2k]

@Mahieddine why you have following line in your expression:

RETURN IF ( COUNTROWS ( SelectedValue1 ) = 1, SelectedValue1 )

[Mahieddine]

The line was present in the code proposed in the article you shared with me, that's why I used the expression.

[parry2k]

@Mahieddine can you paste the actual code instead of the screen shot?

[Mahieddine]

Dynamic Title - progression annuelle =

VAR SelectedValue1 =  SELECTCOLUMNS (

        SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ),

        Parameter[Parameter])

RETURN IF ( COUNTROWS ( SelectedValue1 ) = 1, SelectedValue1 )

VAR SelectedValue2 = SELECTEDVALUE('Comparables Database - Power BI Dashboard'[Sector])

RETURN

    SWITCH (

        TRUE (),

        SelectedValue1 = "EV / Industry Metric",

            SWITCH (

                TRUE (),

                SelectedValue2 = "Infrastructure", "EV / MW",

                SelectedValue2 = "Agriculture", "EV / Hectare",

                "EV / Industry Metric" ),

        SelectedValue1 = "Discount Rate", "Discount Rate",

        SelectedValue1 = "EV / EBITDA LTM", "EV / EBITDA LTM",

        SelectedValue1 = "EV / EBITDA NTM", "EV / EBITDA NTM",

        SelectedValue1 = "EV / Revenue", "EV / Revenue",

         SelectedValue1 = "EV", "EV (in M$)")

[parry2k · Accepted solution]

@Mahieddine I guess selectedvalue  is the issue, read this post to get the selected value.

[Mahieddine]

I tried to replace the SelectedValue as shown in the article you sent me but the solution doesn't seem to work, do you have any idea why ?

[Mahieddine]

Thank you again!

[parry2k]

@Mahieddine can you click the details and provide the error message?

[Mahieddine]

Of course, thank you for the quick reply!

[parry2k]

@Mahieddine there are many ways to solve this:

first, you can use field parameters for the measure selection rather than using a disconnected table (I assume that is what you did)

2nd, you can use dynamic format feature to change the format of the measure based on the selection, check this video on dynamic format:  

From Thousands to Billions: The Power of Dynamic Formatting in Power BI - April 2023 Power BI Update...

Tips for Dynamic Formatting in Power BI - Customize Line and Stack Column Charts | PeryTUS - Power -...

[Mahieddine]

Thank you very much! I used the field parameters instead of my disconnected table and the number format is working perfectly. However, I got a new problem resulting from the change. The table of my graph was based on this measure :

I adjusted it to reflect the changes I made so it can refer to the parameter chosen :

But I'm getting this error after changing the title measure : 

Do you know what may cause this error ?

Thank you very much for your help!