formatting cell if greater than related value

[nick9one1]

I have two tables, the left is a simple table of thresholds for each block. 

The right table is a count of instances for each block 

I would like to create a formatting rule that shades the cells based on the relationship. 

e.g. Conifers for November is 2, which is above the 1 in 20 threshold but below 1 in 50. 

I was hoping to write a measure like this, but it seems you can only use 'related' in row context. 

GreaterThanThreshold = IF([AlarmCount]>RELATED(['Threshold Data'1 in 20'],"1 in 20")

[nick9one1]

Thank you for the replies. Both look like they require me to group the 'issues' table differntly. 

at the moment it is in this format. 

I've made a start by using the summerize command;

Counts = 

SUMMARIZE('issues (2)'
      , ROLLUP( 'issues (2)'[Block]),'issues (2)'[IncidentDateFull]  
      , "Alarms", COUNT('issues (2)'[id])  
      
)

Which gives me this;

I need to figure out how to add an additional filter, as ther are a number of false alarms I'm not interested in. but My initial issue is that this new summary table and my original table dont quite match.

Below the new Summerize is on the left, and the original table on the right. There are a few blocks that have slightly different figures. 

[Anonymous]

Hi @nick9one1 ,hello PhilipTreacy, thank you for your prompt reply!

 
You could also use LOOKUPVALUE function to meet your requirements:

Threshold_20 = LOOKUPVALUE('Threshold Data'[1 in 20 threshold], 'Threshold Data'[Building Name], SELECTEDVALUE('EventCount'[Block]))

The same applies to threshold 50:

Threshold_50 = LOOKUPVALUE('Threshold Data'[1 in 50 threshold], 'Threshold Data'[Building Name], SELECTEDVALUE('EventCount'[Block]))

Create different format measures for different months(can be changed as needed):

Format_September = 
IF(
    SELECTEDVALUE('EventCount'[September]) > 'EventCount'[Threshold_20],
    "pink", 
    ""
)

 Then apply conditional formatting to all month columns using different format measures as shown below:

Result for your reference:

Best regards,

Joyce

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

[PhilipTreacy]

Hi @nick9one1 

Download PBIX file with the example below

The 1 in 20 threshold for Nov for Conifers is 4.35 so 2 is not above that?

You should really have your 'Count' data laid out in a tabular format like this

Power BI and Excel work best wth data laid out like this, if it isn't you run into issues trying to do analysis and reporting.

If you lay the data out like that, you can then use a Matrix visual to show you the same info

But you can now achieve what you want by writing this measure

Exceeds 1 in 20 = 

VAR _Threshold = CALCULATE(MAX('Thresholds'[1 in 20 Threshold]), 'Thresholds'[Building Name] = SELECTEDVALUE(Counts[Block]))

RETURN 

IF( SELECTEDVALUE(Counts[Count]) > _Threshold, "orange")

and using that as the rule for the Conditional Formatting on the matrix cells

giving this

Note that I've adjusted the 1 in 20 Threshold value to be 1 to make this work.  Otherwise there'd be no colour in the cell as 2 is less than 4.35

Doing it this way you only need 1 CF rule.  With your original data laid out with a column for each month/date, you need a rule for each column.

Regards

Phil