Need to apply a conditional formatting to the fist level if the second level has a negative value

[srigag900]

Hi Everyone,

Im currenly facing a serious challenge in Power Bi on applying conditional formating to the first level if the second level has a negative figure. I will give you a snapsot in here

In the above table, i need to color colombo,1006 code which is 53 and Kandy i006 which is 33. If the value flagged in red, then he can drill down fro further analysis. The issue is this. Name, and size (second level) are coming from a different tables.value is coming from three different tables. which is aggregated already. and the location (in columns) are coming from another table.

is there any way to fix this issue? 

[danextian · Accepted solution]

Hi @srigag900 

In a measure, create a virtual summary of the second level column and the value, filter the summary to < 0 and then count the rows. If the count isn't blank and the row isn't level 2, then return the color

Color =
VAR _count =
    COUNTROWS (
        FILTER (
            SUMMARIZECOLUMNS ( Subcategory[Subcategory], "@value", [Sum of Value] ),
            [@value] < 0
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( _count ) ) && NOT ( ISINSCOPE ( Subcategory[Subcategory] ) ),
        "#fdaaaa"
    )

Please see the attached pbix.

[Jihwan_Kim · Accepted solution]

Hi,

I do not know how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Value total: = 
SUM(data[value])

cf measure: = 
VAR _condition =
    IF (
        SUMX (
            ADDCOLUMNS (
                VALUES ( Category[category] ),
                "@condition", IF ( [Value total:] < 0, -1 )
            ),
            [@condition]
        ) < 0,
        -1
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Category[category] ), IF ( [Value total:] < 0, -1 ),
        ISINSCOPE ( 'Name'[name] ), _condition
    )

[danextian · Accepted solution]

Hi @srigag900 

In a measure, create a virtual summary of the second level column and the value, filter the summary to < 0 and then count the rows. If the count isn't blank and the row isn't level 2, then return the color

Color =
VAR _count =
    COUNTROWS (
        FILTER (
            SUMMARIZECOLUMNS ( Subcategory[Subcategory], "@value", [Sum of Value] ),
            [@value] < 0
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( _count ) ) && NOT ( ISINSCOPE ( Subcategory[Subcategory] ) ),
        "#fdaaaa"
    )

Please see the attached pbix.