Forum Discussion

aAmirkHan's avatar
aAmirkHan
Icon for Helper III rankHelper III
3 years ago
Solved

Conditional Formatting with Field Parameter

Hi,

 

I want to get Dynamic color Green for Maximum value and Red for Minimum value in my column chart with field parameter.

Please advise how can it be done?

As per below example

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    3 years ago

    Well, this got really ugly really fast...

     

     

    bar color =
VAR da =
    MAX( 'Date Axis'[Date Axis] )
VAR ma =
    MAX( 'Measure Axis'[Measure Axis] )
VAR ts =
    SWITCH( ma, "Total Sales", [Total Sales], "Unit Sold", [Unit Sold] )
VAR mv0 =
    CALCULATETABLE(
        SUMMARIZE(
            'Date',
            [Stat of Qtr],
            "v", SWITCH( ma, "Total Sales", [Total Sales], "Unit Sold", [Unit Sold] )
        ),
        ALLSELECTED()
    )
VAR mv1 =
    CALCULATETABLE(
        SUMMARIZE(
            'Date',
            [Start of Month],
            "v", SWITCH( ma, "Total Sales", [Total Sales], "Unit Sold", [Unit Sold] )
        ),
        ALLSELECTED()
    )
VAR mv2 =
    CALCULATETABLE(
        SUMMARIZE(
            'Date',
            [Week Start],
            "v", SWITCH( ma, "Total Sales", [Total Sales], "Unit Sold", [Unit Sold] )
        ),
        ALLSELECTED()
    )
VAR mv3 =
    CALCULATETABLE(
        SUMMARIZE(
            'Date',
            [Date],
            "v", SWITCH( ma, "Total Sales", [Total Sales], "Unit Sold", [Unit Sold] )
        ),
        ALLSELECTED()
    )
RETURN
    SWITCH(
        da,
        "Stat of Qtr",
            SWITCH(
                TRUE(),
                ts = MAXX( mv0, [v] ), "green",
                ts = MINX( mv0, [v] ), "red",
                "blue"
            ),
        "Start of Month",
            SWITCH(
                TRUE(),
                ts = MAXX( mv1, [v] ), "green",
                ts = MINX( mv1, [v] ), "red",
                "blue"
            ),
        "Week Start",
            SWITCH(
                TRUE(),
                ts = MAXX( mv2, [v] ), "green",
                ts = MINX( mv2, [v] ), "red",
                "blue"
            ),
        "Date",
            SWITCH(
                TRUE(),
                ts = MAXX( mv3, [v] ), "green",
                ts = MINX( mv3, [v] ), "red",
                "blue"
            )
    )

     

    see attached

5 Replies

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

        Well, this got really ugly really fast...

         

         

        bar color =
VAR da =
    MAX( 'Date Axis'[Date Axis] )
VAR ma =
    MAX( 'Measure Axis'[Measure Axis] )
VAR ts =
    SWITCH( ma, "Total Sales", [Total Sales], "Unit Sold", [Unit Sold] )
VAR mv0 =
    CALCULATETABLE(
        SUMMARIZE(
            'Date',
            [Stat of Qtr],
            "v", SWITCH( ma, "Total Sales", [Total Sales], "Unit Sold", [Unit Sold] )
        ),
        ALLSELECTED()
    )
VAR mv1 =
    CALCULATETABLE(
        SUMMARIZE(
            'Date',
            [Start of Month],
            "v", SWITCH( ma, "Total Sales", [Total Sales], "Unit Sold", [Unit Sold] )
        ),
        ALLSELECTED()
    )
VAR mv2 =
    CALCULATETABLE(
        SUMMARIZE(
            'Date',
            [Week Start],
            "v", SWITCH( ma, "Total Sales", [Total Sales], "Unit Sold", [Unit Sold] )
        ),
        ALLSELECTED()
    )
VAR mv3 =
    CALCULATETABLE(
        SUMMARIZE(
            'Date',
            [Date],
            "v", SWITCH( ma, "Total Sales", [Total Sales], "Unit Sold", [Unit Sold] )
        ),
        ALLSELECTED()
    )
RETURN
    SWITCH(
        da,
        "Stat of Qtr",
            SWITCH(
                TRUE(),
                ts = MAXX( mv0, [v] ), "green",
                ts = MINX( mv0, [v] ), "red",
                "blue"
            ),
        "Start of Month",
            SWITCH(
                TRUE(),
                ts = MAXX( mv1, [v] ), "green",
                ts = MINX( mv1, [v] ), "red",
                "blue"
            ),
        "Week Start",
            SWITCH(
                TRUE(),
                ts = MAXX( mv2, [v] ), "green",
                ts = MINX( mv2, [v] ), "red",
                "blue"
            ),
        "Date",
            SWITCH(
                TRUE(),
                ts = MAXX( mv3, [v] ), "green",
                ts = MINX( mv3, [v] ), "red",
                "blue"
            )
    )

         

        see attached