Conditional Formatting in a Matrix Visual.

[JBF1978]

I have a matrix that I have built, it shows widgets that are built each week. I want to be able to forecast risk areas when we are tight for production based on how many we have available each week.

For example: the "10 GFML", we have 3 we can produce each week (Qty Avaialble), lead time is 1 week, so I would like week 16 and 17 to be shaded red to show this would be an issue. I've tried with adding different measures, but it just adds unnecessary fields into the mix that I don't need. All our lead times are the week prior, so any ones that would be OK would be colored green, any ones that are potentially risky could be yellow, and any ones that are going to cause and issue would be red. This way I can quickly see what my pinch points are by week and by widget. 

Row Labels	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	Qty Available
10 FED			2	2		2	2	2	2	1		2		2	2	1	1		3	2				2	1			1			15
10 TEAM							1				1			1						1					1						1
10 GFED				1		1		1								1	2														5
10 GFML				2		1		2								2	3														3
10 DSM				1		1	2		1		5			1	2	1		1	2	1	1			1	2	1			1	1	5
10 FML			1	1		2	1	1	3	1		2		1	1				3	2								1			3
12 GFML		1			1				1	1		1		2		2			1					1	1						8
12 DSM	1		1	1	1	2	3			3	1	2	4	1	2	1	2		1				1								4
12/14 FED			3		1		2	1	1	2	3	2	3	1	2	2	2	3	1					1	1		1			1	19
12/14 GFED		1							1	1		1				1			1					1							4
12/14 FML			3				2	1	1	1	3	3	1	1	2	1	2	3	1					1			1			1	4
14 GFML																				1											1
16 TEAM				1					1																						1
16 GFED													2	2						1		1		1		1		1			4
16 GFML													2	2						1		1		1		1		1			2

[Anonymous]

Hi @JBF1978 ,
Based on your description, I created some test data, and you mentioned that the delivery time is one week, so that means the quantity of the current week is the production quantity of the previous week, so we can create a measure to determine the quantity that can be delivered in the current week.

PreviousWeekOutput = 
VAR CurrentWeek = MAX('Table'[WeekNum])
VAR PreviousWeekOutput = 
    CALCULATE(
        MAX('Table'[Output]),
        FILTER(
            ALLEXCEPT(
                'Table',
                'Table'[Row Labels]
            ),
            'Table'[WeekNum] = CurrentWeek - 1
        )
    )
RETURN
    PreviousWeekOutput

Then use custom conditional formatting to populate the background color for each piece of data

Color = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[Row Labels]) = "10FML",
    SWITCH(
        TRUE(),
        'Table'[PreviousWeekOutput] >= 3,"Green",
        'Table'[PreviousWeekOutput] = 2, "Yellow",
        'Table'[PreviousWeekOutput] < 2, "Red"
    ),
    SELECTEDVALUE('Table'[Row Labels]) = "10GFML",
    SWITCH(
        TRUE(),
        'Table'[PreviousWeekOutput] >= 3,"Green",
        'Table'[PreviousWeekOutput] < 3, "Red"
    ),
    SELECTEDVALUE('Table'[Row Labels]) = "12DSM",
    SWITCH(
        TRUE(),
        'Table'[PreviousWeekOutput] >= 3,"Green",
        'Table'[PreviousWeekOutput] = 2, "Yellow",
        'Table'[PreviousWeekOutput] < 2, "Red"
    )
)

In order to avoid incomplete displays due to missing data in some weeks, you can create a measure so that each data grid will be formatted with custom conditions

OutputValue = SELECTEDVALUE('Table'[Output]) + 0

Final output

Best regards,
Albert He

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

[JBF1978]

Thanks, this helps a lot! My biggest concern is this is just a small sampling of data, and our Qty Available is dynamic and can change from month to month. For example, One month it may be 3, the next month it may be 4. Is there a way to account for that instead of creating a calculation for each indivual line item, and having that reference that for each?

[Anonymous]

Hi @JBF1978 ,

Of course, I can further explain the implementation logic of the above code, the first level of switch statement is used to go for matching different item names, that is to say, if there are different measures for different items, this level is used for matching. The second level of switch is used to construct the rubric to set the color.
According to your description, if all items use the same judging criteria, you can use the following measure

Color = 
SWITCH(
       TRUE(),
       'Table'[PreviousWeekOutput] >= 3,"Green",
       'Table'[PreviousWeekOutput] = 2, "Yellow",
       'Table'[PreviousWeekOutput] < 2, "Red"
)

For 2 and 3 I have hard coded them here, you can create a separate table of available quantities and then use measure to extract dynamic available quantity criteria to replace 2 and 3. This will enable you to measure these data against different available quantity criteria for each month.

Best regards,
Albert He

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

[lbendlin]

or example: the "10 GFML", we have 3 we can produce each week, lead time is 1 week, so I would like week 16 and 17 to be shaded red to show this would be an issue.

why would that be an issue? What is the importance of the lead time?  Where does the 3 come from?