cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User II
Super User II

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.

 


______________


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. 


excelwithallison.com

Highlighted
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!

Highlighted
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

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors