Forum Discussion

Elcin_7's avatar
Elcin_7
Helper I
2 years ago
Solved

Max and Min Price for Conditional Formating

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's avatar
    Anonymous
    2 years ago

    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.

6 Replies

  • xifeng_L's avatar
    xifeng_L
    Super User
    2 years ago

    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's avatar
      Elcin_7
      Helper I
      2 years ago

      No 😞 still, I am getting the same result.

      • xifeng_L's avatar
        xifeng_L
        Super User
        2 years ago

        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.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Elcin_7 ,

         

        Thanks for your feedback.

         

        Best Regards,
        Adamk Kong