cancel
Showing results for
Did you mean:
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?

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

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".

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

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

Proud to be a Super User!

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.

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

www.excelwithallison.com

Announcements

#### Happy New Year from Power BI

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