Apply color Palette to First Level items based on the Second level calculation.

[Guruprasad7]

Hi All,

Here is the screnario:
I want to apply color Palette to below First Level items based on the sublevel calculation.
First Level:

Under BCO - Second Level / Sub Level items:

Query : Want to Apply color on First Level, based on Second level Least % item.

For Example: First Level item BCO, it is holding the 10 items at Sub Level (after drill thru) & Minimum among the 10 item is 77.84%.

Requirement : Now on the First Level BCO should be also RED as one of the sub level item is RED.  
Logic :  If value is less than 80% then RED.. and few other condition for other colors.

Please help me out here with DAX funciton where we can apply colors.

Thank you in Advance.

Regards,
Guru

[Guruprasad7]

@lbendlin Posted sample data.

I need a DAX to apply below logic:
For BCO, it should be red -> as at the sub level one of the entity is red(Screenshot FYR)

[lbendlin]

see attached.

[Guruprasad7]

@lbendlin  Thanks For your time and help!
But my scenario is little different here , Avg Unit Value is measure containing Cumulative of last 6 month - which we cannot pass as Parameter to MIN(). 

So, I have attached PBI File link below, Please let me know if you can help me with cumulative avg.

Thank you in advance.
Regards,
Guru

[lbendlin]

This formula

Avg Unit Value = 
var selected_date = DATE(YEAR(SELECTEDVALUE('DIM CAL'[Date])),MONTH(SELECTEDVALUE('DIM CAL'[Date])),1)
var Last6 = DATE(YEAR(selected_date),MONTH(selected_date)-5,1)
VAR Cal  = CALCULATE( [AVERAGE UNIT VALUE],FILTER(ALL('DIM CAL'),'DIM CAL'[Date]<=selected_date && 'DIM CAL'[Date]>Last6))
return Cal

is incorrect.  You should use DATEADD or EDATE  to identify the last six months.

Anyway, I don't understand why you cannot pass the cumulative to the minx calculation. Please explain.

[Guruprasad7]

@lbendlin YES Dateadd () can be used but i'm getting correct values using current DAX.
and Cumulative I'm passing it to Minx but it not providing desired results:

Least = MINX( ALLEXCEPT ('FACT DATA', 'FACT DATA'[Plant] ),' DIM CAL' [Avg Unit Value])

Could you Please make changes here to the DAX Formula & make it happen to provided PBI File.
Then you can paste working formula here.

Thanks

[Guruprasad7]

Plant	Tech ID	Avg Unit Value
BCO	BCO-30-1000 UCWT	92.86%
BCO	BCO-BFW (EOE)	95.83%
BCO	BCO-BFW (UTE)	100.00%
BCO	BCO-BOILER	100.00%
BCO	BCO-CHILLED WATER	76.56%
BCO	BCO-CONDENSATE	90.63%
BCO	BCO-HOT WATER	100.00%
BCO	BCO-RX BOILER A	100.00%
BCO	BCO-RX BOILER B	100.00%
BGO	BEO U 1200 CT	89.04%
BGO	BEO U 121 CT	90.83%
BGO	BEO U 122 CT	90.38%
BGO	BEO U 124 CT	93.44%
BGO	BEO U PE CT	91.75%
BGO	BEO U PLP CT	79.34%
BGO	BEO U PP CT	85.84%
BGO	BEO-UCA-B-F1101	98.15%
BGO	BEO-UCA-B-F1101-SAT	100.00%
BGO	BEO-UCA-B-F1101-SUP	100.00%
BGO	BEO-UCA-B-F1102	100.00%
BGO	BEO-UCA-B-F1102-SAT	100.00%
BGO	BEO-UCA-B-F1102-SUP	100.00%
BGO	BEO-UCA-BFW	100.00%
BGO	BEO-UCA-COND-POLSD	100.00%
BGO	BEO-UCA-COND-RET-BAD	91.67%
BGO	BEO-UCA-COND-RET-OKE	94.44%
BGO	BEO-UCA-DEMIN1	100.00%
BGO	BEO-UCA-DEMIN2	100.00%
BGO	BEO-UCA-DEMIN-OUT	100.00%
BGO	BEO-UCB-BFW-V140-30	98.53%
BGO	BEO-UCB-BFW-V140-31	98.61%
BGO	BEO-UCB-BOILER-F143	100.00%
BGO	BEO-UCB-COND-U111	100.00%
BGO	BEO-UCB-DEMIN-U131	94.44%
BLO	BLO-R - 1001C	100.00%
BLO	BLO-U1801 BP1 CWT	96.30%
BLO	BLO-U1802 BP2 CWT	100.00%
BLO	BLO-U1803 BP3 CWT	98.15%
BYO	BYO C CHILLER	90.48%
BYO	BYO CATALLOY CHILLER	100.00%
BYO	BYO CATALLOY JACKET	92.31%
BYO	BYO C-LINE JACKET	92.86%
BYO	BYO D CHILLER	94.44%
BYO	BYO D-LINE JACKET	88.89%
BYO	BYO E CHILLER NH3	100.00%
BYO	BYO E CHILLER PROPYL	95.24%
BYO	BYO E-LINE JACKET	95.24%
BYO	BYO-OLD CLX CWT	95.65%
BYO	BYO-OLD DLX CWT	100.00%
BYO	BYO-OLD ELX CWT	100.00%
BYO	BYO-OLD MARLEY CWT	100.00%
BYO	BYO-OLD MP3 CWT	100.00%
BYO	BYO-OLD MPX CWT	100.00%
BYO	BYO-OLD XL CWT	100.00%

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.