cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Numeric value of average line and conditional formating

Hi, 

 

I want to get the actual number value of the average line that I used analytics to calculate?

 

AnonymousTurtle_0-1599817634173.png

 

Is it possible to somehow get this value and use conditional formating to change the colours of a different matrix e.g. red if the row totals, is under this value, and green if over? If this is possible could someone please explain how to do this too. 

 

Thanks!

3 REPLIES 3
Community Support
Community Support

Hi @AnonymousTurtle ,

Please follow the below steps to achieve it, you can find the details in my sample pbix file.

1. Create a measure as below to defind the color by conditions

Conditional formatting = 
IF (
    SUM ( 'Table'[Value] )
        >= CALCULATE ( AVERAGE ( 'Table'[Value] ), ALL ( 'Table'[Type] ) ),
    "Green",
    "Red"
)​

2. Navigate to "Format" pane and choose Data colors tab. Format the data bar by Field value based on the new created measure "Conditional formatting".Numeric value of average line and conditional formating.JPG

Best Regards

Rena

 

 

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User IV
Super User IV

@AnonymousTurtle , if you visual is a bar with only one measure no legend you can have bar color based on conditional formatting.

 

you can create a conditional formatting measure and use that

 

example

Color Measure =
var _avgDisc = CALCULATE([Discount %],ALL(Retail))
return switch(TRUE(),
[Discount %] <_avgDisc , "Red",
[Discount %] >_avgDisc , "Green", "Black")

 

And use that in conditional formatting with "Field value" option in first box.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User III
Super User III

The analytics should just give a straightforward average of whichever field/measure you selected, so just use AVERAGE function in DAX to create a custom measure for average and use this measure in conditional formatting.

 


______________


Check out my Data Stories Gallery Christmas Report: Are you on Santa's Naughty or Nice List?

______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors