Forum Discussion

Helper I

5 years ago

Solved

conditional format based on calculation in matrix.

Hi

I want to add an arrow on below matrix for each month compared with the previous month.

When critical tickets increase, stay the same or get lower

When Major tickets in crease, stay the same or get lower

if possible in the end should look something like below (arrow can be extra column for each month)

Thanks

MFelix
5 years ago
Hi Tim_H ,

Create the following measure:

Previous_Month_Values = COUNT('Table'[Severity]) - CALCULATE(COUNT('Table'[Severity]); DATEADD('Table'[Date];-1;MONTH))

Now create the condittional formatting based on this measure:

Check PBIX file attach is using a sum and not a count but works in the same way just replace use the syntax above.
matrix_cond.pbix17 KB

4 Replies

MFelix
Super User
5 years ago
Hi Tim_H ,

Create the following measure:

Previous_Month_Values = COUNT('Table'[Severity]) - CALCULATE(COUNT('Table'[Severity]); DATEADD('Table'[Date];-1;MONTH))

Now create the condittional formatting based on this measure:

Check PBIX file attach is using a sum and not a count but works in the same way just replace use the syntax above.
matrix_cond.pbix17 KB
- Tim_H
  Helper I
  5 years ago
  Hi MFelix
  
  Thanks, but not 100% working. I do see arrows but all have same color. I think this is because my 'severity' is not counted as you have in column Quantity but is still line/line for several dates in one month.
  
  Below extract from very uge file ( + 18K tickets)
  
  kr
  
  Tim
  - MFelix
    Super User
    5 years ago
    Hi Tim_H,
    
    A good practice for this type of calculation is to add a Calendar table that related with your date column then you just need to do the following measure for the formatting:
    
    Previous_Month_Values = COUNT('Table'[Severity]) - CALCULATE(COUNT('Table'[Severity]); DATEADD('calendar'[Date];-1;MONTH))
    
    Now do the formatting has previously.
    
    matrix_cond.pbix47 KB