Max and Min Price for Conditional Formating

[Elcin_7]

Hi all,

I have  2 tables Calendar and Raw_Data.

I need min and max price for each month in my table with conditional format.  With the dax formula below. I am getting the result for whole table. I need to highligt Max and min by month . As an example in my table below. 19.46 needs to be green as well because it is min price for Apr. Can you help me please?

Please see the dax formula

Min or Max =

VAR ValuesDisplayed =

    CALCULATETABLE(

        ADDCOLUMNS(

            SUMMARIZE(

                Raw_Data,

                Raw_Data[Part_Number],

                Raw_Data[merchantName],

                Raw_Data[merchantLink],

                'Calendar'[Month Name Short]

            ),

            "TEST", [Price2]

        ),

        ALLSELECTED()      

    )

VAR MinPrice = MINX(ValuesDisplayed, [TEST])

VAR MaxPrice = MAXX(ValuesDisplayed, [TEST])

VAR CurrentPrice = [Price2]

VAR Result =

    SWITCH(

        TRUE(),

        CurrentPrice = MinPrice, 1,

        CurrentPrice = MaxPrice, 2,

        0

    )

RETURN

    Result

I can use table or matrix 

[Anonymous · Accepted solution]

Hi @Elcin_7 ,

You can try formula like below and use it in conditional formatting:

MEASURE =
VAR max_ =
    MAXX (
        FILTER (
            ALL ( Raw_Data ),
            Raw_Data[Part_Number] = MAX ( Raw_Data[Part_Number] )
                && Raw_Data[Month Name Short] = MAX ( Raw_Data[Month Name Short] )
        ),
        [Price2]
    )
VAR min_ =
    MINX (
        FILTER (
            ALL ( Raw_Data ),
            Raw_Data[Part_Number] = MAX ( Raw_Data[Part_Number] )
                && Raw_Data[Month Name Short] = MAX ( Raw_Data[Month Name Short] )
        ),
        [Price2]
    )
RETURN
    IF (
        MAX ( Raw_Data[Price2] ) = max_,
        "red",
        IF ( MAX ( Raw_Data[Price2] ) = min_, "green" )
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Elcin_7]

This logic worked. Thank you.

[Anonymous]

Hi @Elcin_7 ,

Thanks for your feedback.

Best Regards,
Adamk Kong

[xifeng_L]

Hi @Elcin_7 ,

Just remove the month from the SUMMARIZE function.

Min or Max =
VAR ValuesDisplayed =
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE(
                Raw_Data,
                Raw_Data[Part_Number],
                Raw_Data[merchantName],
                Raw_Data[merchantLink]
            ),
            "TEST", [Price2]
        ),
        ALLSELECTED()      
    )

VAR MinPrice = MINX(ValuesDisplayed, [TEST])
VAR MaxPrice = MAXX(ValuesDisplayed, [TEST])
VAR CurrentPrice = [Price2]

VAR Result =
    SWITCH(
        TRUE(),
        CurrentPrice = MinPrice, 1,
        CurrentPrice = MaxPrice, 2,
        0
    )

RETURN
    Result

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[Elcin_7]

No 😞 still, I am getting the same result.

[xifeng_L]

Try removing merchantLink and Part_Number again.

Min or Max =
VAR ValuesDisplayed =
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE(
                Raw_Data,
                Raw_Data[merchantName]
            ),
            "TEST", [Price2]
        ),
        ALLSELECTED()      
    )

VAR MinPrice = MINX(ValuesDisplayed, [TEST])
VAR MaxPrice = MAXX(ValuesDisplayed, [TEST])
VAR CurrentPrice = [Price2]

VAR Result =
    SWITCH(
        TRUE(),
        CurrentPrice = MinPrice, 1,
        CurrentPrice = MaxPrice, 2,
        0
    )

RETURN
    Result

If that doesn't work, you can provide some example data or pbix files so that I can better help you pinpoint the problem.