Conditional Formatting - Row Subtotal Only

[DJLight890]

Hello community,

I have a mockup PowerBI file I created (link at bottom) with dummy data. The visual I need is a matrix and this matrix is essentially a heat map to show which workers are over/under forecasted for projects by month based on the variance (difference).

Managers really want to see the conditional formatting on the row subtotal only. So even if they drill down on the worker to see the projects they are forecasted for, the row subtotals remain red and the drill-down will not show the formatting.

This YouTube video is the closest I found, but is based on user selection and some of the DAX is over my head to try and understand for my data set.

Can someone assist? Drop-box for my pbi mockup:

https://www.dropbox.com/s/z12whwy4jeqahia/Mockup%20-%20Forecasted%20Hours%20PBI.pbix?dl=0

Thank you all in advance!

[PaulDBrown · Accepted solution]

See if this works for you:

Create a measure for the condional formatting along the lines of:

Conditional Formatting =
VAR CF =
    SWITCH (
        TRUE (),
        AND ( [Variance] >= 30, [Variance] < 200 ), 1,
        AND ( [Variance] >= -200, [Variance] < -30 ), 1
    )
RETURN
    IF ( ISINSCOPE ( 'Forecasted Hours by Worker'[Project] ), BLANK (), CF )

and use it in the conditional formatting pane as follows:

[amiller5]

Can you help guide the Dax that I would need to produce a similar result. Only I dont have the variance in my matrix, but I want the formatting to be by Qtr. If Qtr 2 was greater than Q1, the total cell background would be green but if Qtr 3 was lower than Qtr 2, the background color would be red.

[Ashish_Mathur]

Hi,

You must have  a Calendar table with a quarter and Year column and a relationship (Many to One and Single) between the Date column of your Data table to the Date column of your Calendar table.  To your visual, drag Year and Quarter from the Calendar Table.  You can then use the Previousquarter() DAX function to get the value in the previous quarter.  You will then have to write a Variance measure like this

Variance = [Amount]-[Amount in previous quarter].

This variance measure has to be used in conditional formatting to apply your desired colours. 

[DJLight890]

Thank you so much! That worked for the mockup and my actual PBI Data file. I'm glad to know I was at least close in the DAX I was trying. Thanks again!