Row level Conditional formating for a group of rows

[Jagan_MFilterIT]

Hi, 

I have a requirement where i am requierd to show the minimum and maximum values of a group of rows. In the following example, we have a matrix visual which shows the price a product is sold by different sellers. The Matrix hirearchy has the following components; Product Description -> Platform -> Seller. Requirement is to show the lowest and highest price for a product per platform. Also the dataset has dates for the last six months, but the conditional formating takes into account only the last 7 days which is shown. 

I tried the following measure for conditional formatting, but it doesnt work. Request your help on this! 

MaxMin =

var asp = SELECTEDVALUE('0. ITC_KPI'[ASP])

var maxasp = CALCULATE(MAX('0. ITC_KPI'[ASP]),ALLSELECTED('0. ITC_KPI'[inserted_date]))

var minasp = CALCULATE(Min('0. ITC_KPI'[ASP]),ALLSELECTED('0. ITC_KPI'[inserted_date]))

var result =

SWITCH(

TRUE(),

asp=maxasp,"Red",

asp=minasp,"Green"

)

return result

[Anonymous]

Hi @Jagan_MFilterIT ,

Please refer to my pbix file to see if it helps you. Since I don't have the last 7 days of data, I chose the last 23 days of data.

Create a measure.

Measure =
VAR date_ =
    MAX ( '0. ITC_KPI'[asp] )
VAR max_date =
    CALCULATE (
        MAX ( '0. ITC_KPI'[asp] ),
        FILTER (
            ALL ( '0. ITC_KPI' ),
            '0. ITC_KPI'[customer] = MAX ( '0. ITC_KPI'[customer] )
        )
    )
VAR min_date =
    CALCULATE (
        MIN ( '0. ITC_KPI'[asp] ),
        FILTER (
            ALL ( '0. ITC_KPI' ),
            '0. ITC_KPI'[customer] = MAX ( '0. ITC_KPI'[customer] )
        )
    )
VAR result =
    SWITCH ( TRUE (), date_ = max_date, 1, date_ = min_date, 0 )
RETURN
    IF ( MAX ( '0. ITC_KPI'[inserted_date] ) >= TODAY () - 23, result, BLANK () )

Then Create a measure again.

Measure 2=
VAR date_ =
    MAX ( '0. ITC_KPI'[asp] )
VAR max_date =
    CALCULATE (
        MAX ( '0. ITC_KPI'[asp] ),
        FILTER (
            ALL ( '0. ITC_KPI' ),
            '0. ITC_KPI'[customer] = MAX ( '0. ITC_KPI'[customer] )
        )
    )
VAR min_date =
    CALCULATE (
        MIN ( '0. ITC_KPI'[asp] ),
        FILTER (
            ALL ( '0. ITC_KPI' ),
            '0. ITC_KPI'[customer] = MAX ( '0. ITC_KPI'[customer] )
        )
    )
VAR result =
    SWITCH ( TRUE (), date_ = max_date, "Red", date_ = min_date, "Green" )
RETURN
    IF ( MAX ( '0. ITC_KPI'[inserted_date] ) >= TODAY () - 23, result, BLANK () )

Then format the Measure2, select Format>>Conditional formatting>>Advanced controls.

Then custom the rules please.

If I have misunderstood your meaning, please provide your desired output with more details.

Best Regards

Community Support Team _ Polly

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

[Jagan_MFilterIT]

Bascially this logic needs to find min max of price by product by platform for a given date range(last days) @amitchandak. I will try to share some sample data for reference

[amitchandak]

@Jagan_MFilterIT , try like

MaxMin =
var asp = SELECTEDVALUE('0. ITC_KPI'[ASP])
var maxasp = CALCULATE(MAXX(values('0. ITC_KPI'[inserted_date]) , calculate(sum('0. ITC_KPI'[ASP]))),ALLSELECTED('0. ITC_KPI'[inserted_date]))
var minasp = CALCULATE(MinX(values('0. ITC_KPI'[inserted_date]) , calculate(sum('0. ITC_KPI'[ASP]))),ALLSELECTED('0. ITC_KPI'[inserted_date]))

var result =
SWITCH(
TRUE(),
asp=maxasp,"Red",
asp=minasp,"Green"
)

return result

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Jagan_MFilterIT]

Sir, I am also attaching the sample file : https://docs.google.com/spreadsheets/d/1WmDW7PGzAadGMN4qW-pdwKQmandg3n9-/edit?usp=sharing&ouid=10593...

[Jagan_MFilterIT]

Hi Amit, 

Thanks a ton for replying. I tried the above and its actually highlighting all the rows as red, 

Whereas, it should highlight red and green as below: 

It also works differently if i remove the last 7 days filter on the visual..