Forum Discussion
Help with measure and conditional formatting
I have 2 questions i am hoping someone can help with - see table below.
Question 1
I am looking to create a measure which shows % variance based on the benchmark % - i.e. to acheive results shown in red text
Question 2
I am looking to conditional format the values in the Response rate column - based on the varience column, i.e. if below 0 then highlight in red.
| Channel | Response Rate % (this is a calculated Measure - # Responses / # Emails sent) | Benchmark (This is the Median from a number of lines of data from that channel) | Varience (difference between response rate and benchmark) | % Variance against Benchmark |
| 20% | 10% | 10% | 100% | |
| Web | 50% | 45% | 5% | 11% |
| Demo | 55% | 55% | 0% | 0% |
| 45% | 65% | -20% | -31% | |
| Phone | 24% | 30% | -6% | -20% |
Hi stevedata ,
There is no direct subtraction between Measure and calculated column. You can use the function of Max or Sum to find the calculated column and perform the subtraction, such as:
Varience% = [Response Rate%] - MAX('Table'[Benchmark])Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi stevedata ,
Here are the steps you can follow：
1. Create measure.
conditional format = IF( [Varience%] <0,"red","black")2. Format – Conditional formatting -- [Varience%] – Font color – Advanced controls.
3. Enter the Font color interface and set as follows.
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Anonymous thank you
So how do I calculate / what formula do i need to calculate values in the 3rd column?
I need to find the difference between the two (which will be the varience) but one of the fields is a measure, but the other one is a column.
When I figure out how to do that then I can use your method to conditional format.
Many thanks,
Steve
Hi stevedata ,
There is no direct subtraction between Measure and calculated column. You can use the function of Max or Sum to find the calculated column and perform the subtraction, such as:
Varience% = [Response Rate%] - MAX('Table'[Benchmark])Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
