Conditional formatting across 4 measures

[Vashko]

Hello all,

I have a bit strange request so I will lay out the problem as best as I can.

Basically the idea is to have gradient applied from left to right instead of from top to bottom, but the issue is the columns are 4 seperate measures. 

Does anybody have any pointers for me?

Thanks in advance

[v-yueyunzh-msft · Accepted solution]

Hi, @Vashko 

According to your description, you want to " have gradient applied from left to right instead of from top to bottom, but the issue is the columns are 4 seperate measures."

Because our default color format is for the entire table; so this can be a little complicated, you can refer to.

Here are the steps you can refer to :
(1)This is my test data:

(2)We need to create a new table as a dimension table like this('Table 2'):

(3)Then we need to create two measures like this:

Value Measure = VAR _t=ADDCOLUMNS( SUMMARIZE(ALLSELECTED('Table'),'Table'[Category],'Table'[SubCategory]) , "QTD" , [QTD Measure] ,"6 Mth" , [6 Month Measure],
 "12 Mth" , [12 Month Measure] , "2Yr" , [2Yr Measure])
 var _cur_column =  MAX('Table2'[Column1])
 var _cat = VALUES('Table'[Category])
 var _sub = VALUES( 'Table'[SubCategory])
 var _t2= FILTER( _t,  [Category] in  _cat && [SubCategory] in  _sub)
 return
 SWITCH( _cur_column , 
 "QTD" ,  SUMX(_t2, [QTD]),
 "6Mth",SUMX(_t2, [6 Mth]),
 "12Mth",SUMX(_t2, [12 Mth]),
 "2Yr",SUMX(_t2, [2Yr])
 )

Measure /Color = VAR _t=ADDCOLUMNS( SUMMARIZE(ALLSELECTED('Table'),'Table'[Category],'Table'[SubCategory]) , "QTD" , [QTD Measure] ,"6 Mth" , [6 Month Measure],
 "12 Mth" , [12 Month Measure] , "2Yr" , [2Yr Measure])
 var _cat= VALUES('Table'[Category])
 var _sub = VALUES('Table'[SubCategory])
var _t2= FILTER( _t , [Category] in _cat && [SubCategory]  in _sub)
var _list = {sumx(_t2 , [QTD]),SUMX(_t2 ,[6 Mth]), sumx(_t2, [12 Mth]),SUMX(_t2,[2Yr])}
var _value_measure=  [Value Measure]
var _rank =COUNTROWS( DISTINCT( FILTER(_list, [Value] <_value_measure)))+1
 var _column_count = COUNTROWS(ALL('Table2'))
 var _percentage=_rank/_column_count
 return
 "rgba(80,210,170,"&_percentage&")"

(4)Then we can put the [Value Measure] on the visual and we need to configure the [Measure /Color] as the format:

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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