Format background of blank cells in matrix

[EpicTriffid]

Hello all!

The problem:

I am trying to get all cells specified as Actuals im the below matrix to have a grey background colour. Just for differentiation. However, I can't just default them to having zero as a zero means something different from the blank in this dataset. 

The code for what I've got so far is fine for colouring in figures with actuals, but getting those blank cells under actuals filled in is boggling my brain.

Q2 Actuals BG = 
IF(
    SELECTEDVALUE(Query2[Forecast/Actuals]) = "Actuals", "#d3d3d3")

Any help?

[EpicTriffid]

Is there any help on this? I feel like I've dealt with blanks before in a matrix but cannot remember what I did?

[EpicTriffid]

Hi @v-zhangti 

Unfortunately that doesn't work either as the format of the data is closer to:

So the matrix is inferring the context of data with missing years and putting a blank in it's place, generating the below.

Using the same logic in my measures of only colouring in "Actuals" as grey still gives me the below. I can't seem to select those blanks under Actuals using DAX to make them colour grey.

 

Does that make sense?

[v-zhangti]

Hi, @EpicTriffid 

You can try the following methods.
Sample data:

Color = IF(
    SELECTEDVALUE('Table'[Forecast/Actuals]) = "Actuals", "#d3d3d3")

Measure = SUM('Table'[Value])+0

Best Regards,

Community Support Team _Charlotte

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

[EpicTriffid]

I can get quite close to this but i just can't seem to make the conditional formatting affect the blank cells:

I also know that technically, in the table operating behind this matrix that essentially there are no rows that relate to the blank cells, so I'm trying to make Power BI only affect what is technically "missing" from the data, but I don't know how to get there!

[eliasayy]

hello please try:

Conditional Formatting =
VAR SelectedActual = 
    SELECTEDVALUE(Query2[Forecast/Actuals])
VAR Table =
    CALCULATETABLE (
        Table,
        REMOVEFILTERS (
            //All your dates example
            Table[Date],
            Table[Month]
        )
    )
VAR summaryof =
    SUMMARIZE ( Table, [Date], "Actual Forcast", [amount of all actual and forcast] )
VAR T3 =
    FILTER ( summaryof, SelectedActual  = "Actuals" )

RETURN
    IF(
    T3, "#d3d3d3")

[eliasayy]

Hello 

Var table is making sure all values are selected

Var summary is summarizing the table with your measure

Var t3 is filtering the summarized table into only actuals

[EpicTriffid]

Hi @eliasayy 

Unfortunately that doesn't work. It still does not show any formatting of the background against any blank values.

[EpicTriffid]

Hey! Thanks for the reply and your time! I'm afraid I can't implement this as I'm not entirely sure what it's doing?