Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
GS76
Frequent Visitor

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:

 

GS76_0-1736503574392.png

 

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:

 

GS76_1-1736503697954.png

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):

 

GS76_2-1736504146697.png

 

However when I use these measures in the conditional formatting for the axis range the axis doesn't change to these numbers:

 

GS76_3-1736504344345.png

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

 

1 ACCEPTED SOLUTION

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

View solution in original post

9 REPLIES 9
MFelix
Super User
Super User

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



GS76
Frequent Visitor

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



GS76
Frequent Visitor

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.

vstephenmsft_1-1736820971425.png

 

 

 

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

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

Sometimes it's the magical moments that happen. Anyway, glad your problem was solved, @GS76 .

If possible, please mark your reply as solution. It is beneficial for future people to quickly check answer.

 

Best Regards,

Stephen Tao

bhanu_gautam
Super User
Super User

@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.9

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Hi, 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?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.