Conditional Formatting based on result of a measure

[LYorkToenniges]

Hello and thank you for any help you can provide.

I have two rows of card, one aggragated to one level (region) above the other (teams) and would like the font color to change when the result of the teams values is greater than the average of the region value.  I am getting lost in the sauce writting the dax for it because I have very little experience with calculate (I assume that's what I need to change the aggragation level via filters).

This is the current set up I have (I cut around some sensitive info) the top is the regions stats, the bottom is the team stats.  I would like some of the callout values to highlight green when above/below the region value depending on which category it is.  These are both aggragations of values that go down another level (pods) so I can't just hardcode the values.

How would I write a dax measure that allows me to accomplish this?  I was thinking something like:

=if(calculate(<average of measure, filtered to team)>calculate(average of measure, filtered up to region), "Green") (I have hex codes for the actual color)

Please let me know what I have left out, I don't feel I have explained it very well but am unsure how else to word it.

Example data: Call Scores would be an average, CTCs would be a sum

Month	Region	Team	Pod	Call Score	CTCs
January	John Smith	Pistons	Hot Pot	0.9675	23
January	John Smith	Lakers	Sushi	0.9682	13
January	Jane Doe	Bears	Bagels	0.9865	15
January	Jane Doe	Bears	Donuts	0.9543	21
January	John Smith	Lakers	Nigiri	0.9642	19

[DataInsights]

@LYorkToenniges,

You could try measures like these. The function ALLSELECTED clears the filter context from within the visual, keeping external filters such as slicers. The VALUES function captures the filter context for the specified column.

Average Call Score = AVERAGE ( 'Table'[Call Score] )

Conditional Formatting Measure =
VAR vTeamAmount =
    CALCULATE ( [Average Call Score], ALLSELECTED (), VALUES ( 'Table'[Team] ) )
VAR vRegionAmount =
    CALCULATE ( [Average Call Score], ALLSELECTED (), VALUES ( 'Table'[Region] ) )
VAR vResult =
    IF ( vTeamAmount > vRegionAmount, "Green", "Other Color" )
RETURN
    vResult

[LYorkToenniges]

I think this would work if I didn't have a slicer for team working on the page.  When I try the individual pieces they result in the same value as the team card has, not the team and the region.

[DataInsights · Accepted solution]

@LYorkToenniges,

Since there's a Team slicer, you can adjust the vTeamAmount variable to simply be the underlying average measure. The vRegionAmount variable can be adusted to use ALL which removes all filter context, keeping only the Region.

Conditional Formatting Measure =
VAR vTeamAmount = [Average Call Score]
VAR vRegionAmount =
    CALCULATE ( [Average Call Score], ALL ( 'Table' ), VALUES ( 'Table'[Region] ) )
VAR vResult =
    IF ( vTeamAmount > vRegionAmount, "Green", "Other Color" )
RETURN
    vResult

[LYorkToenniges]

Thank you very much!  It worked perfectly.  I really need to find some good tutorials (and free time) to wrap my head around filter context issues.  I really struggle with it.

[DataInsights]

@LYorkToenniges,

Glad to hear that works. Filter context can be challenging. The book below is the best resource I've found on the topic:

https://www.sqlbi.com/books/the-definitive-guide-to-dax/ 

[LYorkToenniges]

Thank you!  Looks like I have some reading to do!

[LYorkToenniges]

Thank you so much, I will give this a whirl next chance I get and let you know how it goes!