Coloring Values Assistance

[PrivateAnalytic]

Hello community!

I have created a matrix and a table on the same tab of a Power BI Report. (See 1st picture below)

The table on the left uses conditional columns which I manually inserted into the table. (For example: If the time is 8:00 AM, then 15 staff is then 49, and so on and so fourth). Both visuals are useing the same times. The values in the matrix are using a column called StudentID, and the values are as Count (Distinct).

I was asked to find a way to color the values on the right as follows:

• If the Value is above the 5 Staff number, then color it Green
• If the Value is below the 15 Staff number, then color it Red
  • Ex: On January 25, 2022 the value is 30. This value would be Red
• If Value is in between, then color is Yellow 

Here is an example of the data I am using

Time	Date	StudentName	StudentID	College	CreditHours	15 Staff	5 Staff
8:00 AM	January 27, 2022	Joseph Mann	005866	Business	14	49	51
9:00 AM	January 29, 2022	John Doe	447856	Business	16	77	79
9:30 AM	January 25, 2022	Jane Doe	555013	Law	18	70	72
10:00 AM	January 25, 2022	Ryan Pierce	554876	Graduate	16	61	63
8:00 AM	January 26, 2022	Jannette Richardson	332146	Education	15	49	51
12:00 PM	January 27, 2022	Dameon Travis	958741	Business	15	45	47
2:00 PM	January 28, 2022	Marney Phillips	004584	Education	15	63	65

I am not really 100% sure how this would be able to get formatted the way I am being asked. Would this be using Variables in a calculation? I struggle so much with variables and some DAX Functions. I've tried to do drop down on the values and conditionally format, however that really didn't work for my case.

I did attempt to do conditional formatting. However with the rules, I am not able to do conditional formatting based on both 15 Staff and 5 Staff. So it is being sort of odd with it because I am trying to compare the Value between both 15 and 5 Staff. Is there some sort of DAX Expression that can maybe be written to compare the value between both 15 and 5 Staff?

[DataInsights · Accepted solution]

@PrivateAnalytic,

You can achieve this with a measure that determines the color:

Color Measure = 
VAR vCount = [Count StudentID]
VAR v15Staff =
    MAX ( Table1[15 Staff] )
VAR v5Staff =
    MAX ( Table1[5 Staff] )
VAR vResult =
    // use color names or hex codes
    SWITCH ( TRUE, vCount > v5Staff, "Green", vCount < v15Staff, "#E6113C", "Yellow" ) 
RETURN
    vResult

Create conditional formatting for the measure [Count StudentID]:

[v-zhangti · Accepted solution]

Hi, @PrivateAnalytic 

Based on the data you have provided, you can refer to the following methods.

Measure = 
Var N1=DISTINCTCOUNT('Table'[StudentID])
return
SWITCH(TRUE(),
N1>SELECTEDVALUE('Table'[5 Staff]),"Green",
N1<SELECTEDVALUE('Table'[15 Staff]),"Red",
"Yellow")

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[DataInsights · Accepted solution]

@PrivateAnalytic,

You can achieve this with a measure that determines the color:

Color Measure = 
VAR vCount = [Count StudentID]
VAR v15Staff =
    MAX ( Table1[15 Staff] )
VAR v5Staff =
    MAX ( Table1[5 Staff] )
VAR vResult =
    // use color names or hex codes
    SWITCH ( TRUE, vCount > v5Staff, "Green", vCount < v15Staff, "#E6113C", "Yellow" ) 
RETURN
    vResult

Create conditional formatting for the measure [Count StudentID]: