Table of values - Need Conditional Formatting of each value against its target in the row above

[Ptown]

Hi,

I have a monthly pipeline report that shows how opportunities are progressing through a sales process. So far, thanks to help from this community, I have manipulated my source data in Power BI to the point where I have the exact the table I require.

It is a table that summarises the value of projects, split by sector, that have passed through each phase in a given month. And also the target values.

Here is a dummy PBIX file that has an example table in. https://1drv.ms/u/s!AtJBzwB0OS_Qgz_17trIoDazkoht?e=udfkfV

(Thank you again to @parry2k for the initial solution.)

The final step that I am trying to implement is to apply conditional formatting so each actual is green if it is equal or greater than the target, and red if not.

I have tried to apply conditional formatting rules but haven't been able to get it to work. I can't even get the targets to remain white. 

I think I need to create an interim table that has ones and zeros, and then use the 'Format by' 'Rules' and 'Based on field' to look at the interim table. I've done this in Excel and then imported to Power BI, but that's not a solution I can use practically.  I'm just not sure how to manipulate the data in the same way in Power BI.

Thank you in advance for any help.

[v-eachen-msft · Accepted solution]

Hi @Ptown ,

You could create a measure to configure it.

Measure =
VAR a =
    CALCULATE (
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[Target or actual?] = "Actual" )
        ),
        ALLEXCEPT ( 'Table', 'Table'[Sector], 'Table'[Phase] )
    )
VAR b =
    CALCULATE (
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[Target or actual?] = "Target" )
        ),
        ALLEXCEPT ( 'Table', 'Table'[Sector], 'Table'[Phase] )
    )
RETURN
    IF (
        a - b < 0
            && SELECTEDVALUE ( 'Table'[Target or actual?] ) <> "Target",
        "red",
        IF ( SELECTEDVALUE ( 'Table'[Target or actual?] ) = "Target", "white", "green" )
    )

Here is my test file for your reference.

[Ptown]

@v-eachen-msft  Thank you very much! That is great and I have been able to apply it to my real data.

Do you know how I can make the blank actual cells  be red as well?

In my example there were no opportunities contributing towards the actual for Sector-B, phase 3. The target in the example was $3,100). The actual cell in the table is blank and has no colour in your solution. I'd like it be red (and I don't mind whether it shows a zero or is blank).