Forum Discussion
Graph range conditional formatting not working with a measure
Thanks, I seem to have resolved it. I rebuilt the graph and now it's working correctly when I filter on the area so not sure what was different the first time around. I didn't need to change the measure. Note I did do a quick check as you have above with a table the same as my graph dimensions and I get a different value for each year & month as you have in your table but when I use it in the axis it takes the value that shows when I put the measure in the card, i.e the value of the total line in the table
Hi GS76 ,
You problem is that the result for the maximum and minimum have more that one value per month so when you get to the overall y-axis it returns more than one value so it does not work.
For this you need to pickup the MAX and MIN for all selected months try to add the following code:
Minimum = MINX(ALLSELECTED(TABLE[Month]), [Min Line for Axis])
Maximum = MAXX(ALLSELECTED(TABLE[Month]), [Max Line for Axis])
Be aware that the Table[Month] is the values you are using has X-axis on your chart, this should give you the expected result.
I'm not quite sure how I'm writing that. I've created the table MinLine with the measure Total Sales Per Volume and then I'm taking the Minx value of the measure in that table i.e.
Minx(MinLine,Total Sales Per Volume) where am I adding in the date part if I do:
Minx(allselected('Sales'[Date],MinLine,Total Sales Per Volume) I get an error.
A single value for date in table 'Sales cannot be determined.
Also in this part I'm getting the measure for each of the 12 months at month level:
SUMMARIZECOLUMNS(
'Sales'[Year & Month],
DATESBETWEEN('Sales'[Date],_min,_max),
"Total Sales Per Volume", DIVIDE(SUM('Sales'[Sales]),SUM('Sales'[Volume]))
And then when I do minx of this table does this not give me one number? Note this works fine in the axis when I don't select an area from the table. It's only when I select the area that the number isn't calculated correctly
Hi GS76 ,
This is context related, can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.I'm not allowed to share the PBIX file it's against our companies confidentiality rules. I will try and spend some time next week mocking up some example data to share.
Hi GS76 ,
Is there any updates?
As Miguel said, it is context related. If you want to check if your conditional formatting returns correct result, you should keep it in the same context. Here, when you put it in the card, it's already missing a lot of relevance to your bar and line chart.
You could check it by duplicate the second bar and line chart and change it to the table visual. And then put your measures that you used as conditional formatting into the table visual.
Based on this, you can more clearly determine where there is a problem with the way the measure is written.
Here I made a sample for detailed explanation.
As you can see, the measure is "MAX('Table'[Sales])". It returns the max value.
In the card visual, it shows the max sales value of the Table. However, in the table visual, it shows the max sales value of each row due to the context. For example,the max value is 30 in 2023.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly