Forum Discussion
Graph range conditional formatting not working with a measure
Hi,
I have a chart which shows some sales data for the last 12 rolling months:
I have created a measure that works out the minimum value of the red line across the 12 months and the max value of the blue line across the 12 months. I then put these in the y-axis and secondary y-axis range and this works fine:
However I have another table on the page which splits my sales data by area. When I click on an area in that table the value of my measures changes as expected. i.e. I click on area A and my graph changes to reflect Area A and the min measure and the max measure match what I would expect in the axis (cards showing what the measures are):
However when I use these measures in the conditional formatting for the axis range the axis doesn't change to these numbers:
The min axis should be 1.49 and the max should be 5.8 but it doesn't show this.
The measure for the min is:
Min Line for Axis =
var _max = eomonth(today(),0)
var _min = eomonth(_max,-12)+1
VAR MinLine =
SUMMARIZECOLUMNS(
'Sales'[Year & Month],
DATESBETWEEN('Sales'[Date],_min,_max),
"Total Cost Per Volume", ABS(DIVIDE(SUM('Sales'[Cost]),SUM('Sales'[Volume]))))
RETURN
MINX(MinLine,[Total Cost Per Volume])*0.9
The max measure is:
Max Line for Axis =
var _max = eomonth(today(),0)
var _min = eomonth(_max,-12)+1
VAR MaxLine =
SUMMARIZECOLUMNS(
'Sales'[Year & Month],
DATESBETWEEN('Sales'[Date],_min,_max),
"Total Sales Per Volume", DIVIDE(SUM('Sales'[Sales]),SUM('Sales'[Volume])))
RETURN
MAXX(MaxLine,[Total Sales Per Volume])*1.1
So in summary, if I put these measures into cards and click on the different areas in the area table these change as expected. However when I put them in the condtional format part for the axis these don't change as expected. What am I missing?
Thanks
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
9 Replies
GS76 , Try using
Min Line for Axis =
VAR _max = EOMONTH(TODAY(), 0)
VAR _min = EOMONTH(_max, -12) + 1
VAR MinLine =
SUMMARIZECOLUMNS(
'Sales'[Year & Month],
DATESBETWEEN('Sales'[Date], _min, _max),
"Total Cost Per Volume", ABS(DIVIDE(SUM('Sales'[Cost]), SUM('Sales'[Volume])))
)
RETURN
MINX(MinLine, [Total Cost Per Volume]) * 0.9Max Line for Axis =
VAR _max = EOMONTH(TODAY(), 0)
VAR _min = EOMONTH(_max, -12) + 1
VAR MaxLine =
SUMMARIZECOLUMNS(
'Sales'[Year & Month],
DATESBETWEEN('Sales'[Date], _min, _max),
"Total Sales Per Volume", DIVIDE(SUM('Sales'[Sales]), SUM('Sales'[Volume]))
)
RETURN
MAXX(MaxLine, [Total Sales Per Volume]) * 1.1Hi, thanks for your reply, how is this different to what I have already? I realised I made a typo on the max measure part and it should say maxx not minx but was there something else I'd made a mistake on?
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
