Forum Discussion

JBF1978's avatar
JBF1978
Regular Visitor
1 year ago

Conditional Formatting in a Matrix Visual.

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. ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    JBF1978
    Regular Visitor
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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