Forum Discussion
Different Conditional formatting rules for each value in x-axis
- Have you created a relationship between the Target table and the Store or Sales table? If you do this, and the column chart has StoreID on the Axis, then the MAX() part of the formula should find the MAX target% for the selected storeID in the column visual. If you're trying to do this for more than one store at a time, you'll need to create a more complex measure and explain further what you need.
You should be able to use SELECTEDVALUE(Target[Target]) instead of MAX if that makes more sense.
If you want to you could build out the DAX further to add some error checking:
IF(HASONEVALUE(StoreID), Measure)
You'll need to provide a bit more info about your data model, column and table names, relationships, etc if you want detailed help from the community.
I would also recommend storing the Target values as a table in Power BI, something like:
| StoreID | Target |
| A | 5% |
| B | 1% |
| C | .75% |
| D | 3% |
| E | 1% |
| F | 1.5% |
Then use that Target table in a measure, similar to:
IF([Sales%]>MAX(Target[Target]), "Green", "Red")
Note this doesn't account for between, in order to do that you will need to define what you mean by 'between', either as a percentage of the target, or as another column in the target table.
Hope that makes sense.
That makes sense AllisonKennedy . The sales% for each store is a measure, which is calculated as SUM(Sales)/SUM(Total Sales). I created the table as you suggested with StoreID and Target. I understand your formula of IF([Sales%]>MAX(Target[Target]), "Green", "Red"). However, that formula when added as conditional formatting only makes Store A red since it's the only one above 5%. I'm assuming that the column chart isn't looking for the MAX of the Target of each respective store, but the MAX of all Targets. What formula would I need so that I get something like this:
I think there's something I'm missing. Thanks again.
- Have you created a relationship between the Target table and the Store or Sales table? If you do this, and the column chart has StoreID on the Axis, then the MAX() part of the formula should find the MAX target% for the selected storeID in the column visual. If you're trying to do this for more than one store at a time, you'll need to create a more complex measure and explain further what you need.
You should be able to use SELECTEDVALUE(Target[Target]) instead of MAX if that makes more sense.
If you want to you could build out the DAX further to add some error checking:
IF(HASONEVALUE(StoreID), Measure)Thanks AllisonKennedy I got it to work. I used your solution. The problem was in my relationship. I needed to set it as Both for Cross filter direction. I was also able to build in the yellow range.
Hi,
Share the link from where i can download your PBI file.
