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....
  • 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.

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.