Forum Discussion
Issue with measure
Hello and thank you in advance for any help. I have two tables called Comp Salary and Comp SUM. The Comp Salary table has salaries for multiple people across multiple job codes (each job code has several people assigned to it with different salaries). The Comp SUM table has the predetermined market ranges for each job code. I have the below table chart set up and want to have a new column that shows a green arrow when the average salary is above the Median and a red arrow for when the average salary is below the median range. For example, J1008 would have a red down arrow because the average salary of $27.23 is less than the Median of $28.75. J0913 would have a green up arrow because the average salary is higher than the median. The Average salary is a measure and the median is just a regular column from the source table (I'm not sure if that is important information to know)
The problem I am running in to is that every google search for how to do this wants me to create another measure to calculate the Median off of the salary. Since I already have the Median rate for each job code, that won't work. Does anyone know a way I could do this? Thank you in advance.
Hi christinas ,
Your google search are correct you need in fact a new calculation in order to get it to properly do the condittional formating because in Power BI the conditional formatting can be done in 3 different manners (gradient, rules and calculation) but all of them are always around a single value is not for example has in excel where you can add a calculations (ex: A1 >= B1).
However if this is a single use case and you only want to have that comparision in this specific visual, so no reusage of the formatting you can do that calculation using Visual Calculations.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview
Then on the conditional formatting you can use that calculation to do the formatting.
Steps Below:
1. Create the visual calculation
2. Add the formula:
Difference to median = [Average Salary] - [Market Median]Call it whateve you find more appropiated
3. Do the condittional formatting on top of the difference
Final result
3 Replies
Hi christinas ,
Your google search are correct you need in fact a new calculation in order to get it to properly do the condittional formating because in Power BI the conditional formatting can be done in 3 different manners (gradient, rules and calculation) but all of them are always around a single value is not for example has in excel where you can add a calculations (ex: A1 >= B1).
However if this is a single use case and you only want to have that comparision in this specific visual, so no reusage of the formatting you can do that calculation using Visual Calculations.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview
Then on the conditional formatting you can use that calculation to do the formatting.
Steps Below:
1. Create the visual calculation
2. Add the formula:
Difference to median = [Average Salary] - [Market Median]Call it whateve you find more appropiated
3. Do the condittional formatting on top of the difference
Final result
Hi MFelix . Thank you for your response. I will try it out.
MFelix IT WORKED PERFECTLY! Your step by step instructions and pictures were very helpful. Thank you again.
