Hi,
I need some help with DAX / conditional formatting.
We have a survey for employees. The survey is a total of 32 questions, which provides data across 8 dimensions. Each dimension has 4 separate questions.
I have a table with rows of company locations, and columns of each dimension. The intersection of a dimension and location is the average score of that dimension at that location.
I would like to conditionally format a value of a location with green shading if it is more than 10% above the overall average of the dimension, and red shading if it is more than 10% below the overall average of the dimension.
My values for the table are configured as follows (four questions equate to one dimension):
First I create a measure for the SUM of the survey score of each question (my numerator):
Q01 Num = SUM(‘Ethical Pulse - Raw Data’[Question 1.])
Q02 Num = SUM(‘Ethical Pulse - Raw Data’[Question 2.])
Q03 Num = SUM(‘Ethical Pulse - Raw Data’[Question 3.])
Q04 Num = SUM(‘Ethical Pulse - Raw Data’[Question 4.])
Then I create a measure for the COUNT of the number of responses to each question (my denominator):
Q01 Den = COUNTA(‘Ethical Pulse - Raw Data’[Question 1.])
Q02 Den = COUNTA(‘Ethical Pulse - Raw Data’[Question 2.])
Q03 Den = COUNTA(‘Ethical Pulse - Raw Data’[Question 3.])
Q04 Den = COUNTA(‘Ethical Pulse - Raw Data’[Question 4.])
Then I create a measure for the value of the Overall Dimension average:
Dimension Avg Value = ROUND ( DIVIDE ( Q01 Num + Q02 Num + Q03 Num + Q04 Num),(Q01 Den + Q02 Den + Q03 Den + Q04 Den),0),2)
I do not claim that what I’ve done above is the right / best way to do this. Honestly, I think I may be making it much harder / complicated than it needs to be. Nevertheless, this is as far as I’ve gotten. I have a table with the data that I’m expecting, I just need to figure out how to conditionally format it.
I would appreciate it if someone could suggest what I should do from here? Is there a specific set of DAX training in EDNA that would help me learn what the right solution is? If I can at least get a conceptual idea of what I need to do to solve this, that would be very much appreciated. Thank you!