Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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.
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
@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.
Sir, I am also attaching the sample file : https://docs.google.com/spreadsheets/d/1WmDW7PGzAadGMN4qW-pdwKQmandg3n9-/edit?usp=sharing&ouid=10593...
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..
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |