How to set background of matrix with background color based on row & column header

[Amit_Saxena]

Hi, I need help in defining the background color of matrix in combination of matrix row and column headers, for example i have buckets in both row and column headers of matrix for current(column) and previous(row) months.

I have created the two columns one defines the values of Previous month

Previous color formula

Other one has the current month values

Now i have created the other column as Color:

I am using this color formula in value field(count color) and using conditional formatting filed value(count of Color)as

I am able to get the desired result but only problem here is that if matrix cell has null values it doesn't showcase the color,

this is the problem i have, I don't want to place the image in the background, pls suggest?

Here is the required output(pls look at the background color in combination with row and column headers):

Tablein Required format

And here is the output:

Top image has Output and bottom image is the required outcome

Thanks, Amit

[Anonymous]

Hey, what's up?

I'm having a problem similar to the one you raised and I wanted to know if you'd found a way to solve it.

Thank you very much.

Best regards

Miracles.

[Amit_Saxena]

Hi Sorry for the late response, no have used the image instead.

[jdbuchanan71]

@Amit_Saxena 

We can do it but it takes a couple of steps. The problem is, in your example the intersection of
Current Month: "Above 500"
Previous Month: "Zero"
does not exist. It's not = 0, it is NULL which means PowerBI does not see it at all in order to apply the color.

We can create fake results by using a measure like so

Zero Value = 
VAR _tbl =
    CROSSJOIN ( 
        VALUES ( Data[Current Month] ), 
        VALUES ( Data[Previous Month] ) 
    )
RETURN
    COUNTROWS ( _tbl ) * 0

This will return a 0 on every combination of [Current Month] and [Previois Month]

Then we add that into your measure simply by putting '+ [Zero Value] on the end of your measure.

Now we get a table with results in every field and finally we can use a formatting measure to format the background.

Formatting measure:

Color = 
VAR _CM = SELECTEDVALUE ( Data[Current Month] )
VAR _PM = SELECTEDVALUE ( Data[Previous Month] )
RETURN
    SWITCH (
        TRUE (),
        _CM = "Zero" || _CM = "Below 100", "Red",
        _CM = "100 to 400" || _CM = "Above 500", SWITCH (
            TRUE (),
            _PM = "Zero" || _PM = "Below 100", "Yellow",
            _PM = "100 to 400" || _PM = "Above 500", "Green"
        )
    )

I have attached my sample file for you to look at.

[Amit_Saxena]

Thanks for your response, Hope this should work, I will check this.

[v-kelly-msft]

Hi @Amit_Saxena .

First create a table with the column header of the matrix, see as below:

Then create a measure as below:

Measure = IF(MAX('Table'[Bucket Previous month])="zero"&&MAX('Table 2'[Bucket Previous month])="zero",1,
IF(MAX('Table'[Bucket Previous month])="Below 100"&&MAX('Table 2'[Bucket Previous month])="Below 100",2,
IF(MAX('Table'[Bucket Previous month])="100 to 400"&&MAX('Table 2'[Bucket Previous month])="100 to 400",3,
IF(MAX('Table'[Bucket Previous month])="above 500"&&MAX('Table 2'[Bucket Previous month])="above 500",4,BLANK()))))

This is a way to avoid invalid values in the matrix.

Then add a conditional format for the measure.

Finally you will see:

In the matirx visual ,you need to use the column of table 1 as rows, and the column of table 2 as columns.

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Amit_Saxena]

Hi @v-kelly-msft

Thanks for your response! if you see my screen shot and the kind of view i need, I want to show the specific and different color in blank cells for example last two rows should have red background color . I need three differerent colrs in differnt location, pls review the screen shot as required view

[v-kelly-msft]

Hi @Amit_Saxena ,

Sorry but for the same value,currently we can only define one color .

 You can come up with a new idea and add your comments there to make this feature coming sooner. https://ideas.powerbi.com/forums/265200-power-bi-ideas

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

Would it be acceptable to show 0 instead of blank? Then the background color should activate.