COUNTIF based on Averages in displayed table

Question

Hi TeamBI!Thanks SOOO much for looking at my question.&nbsp;I have a visual - its a table.Each of the 7 columns in the visual table incldes a number between 0 -7. (Based on 7 columns in the dataverse table)These numbers are set as Averages by the visual, drawing on the dataverse data.Conditional formatting turns the Average green in the visual table if it is greater than or equal to 5.The displayed averages numbers are altered based on a date filter slicer for the sheet.&nbsp;SoAs you change the dates in the filter slicer, the number of datapoints in the dataset changes and therefore the average being displayed in each of the boxes in the visual table changes.&nbsp;What I want to do is count the number of numbers in each row that have gone green.So if 5 of the averages being shown are green, then the answer is 5.If the date slicer changes and the averages in the visual table change, and now only 3 are green, then this changes to 3.&nbsp;I dont think this can be done in the data table, because the count is based on displayed averages which change when the date filter is applied.&nbsp;In Excel it would simply be COUNTIF(A1:G1,&gt;=5), referencing that table in the visual.&nbsp;But I dont think that is possible here.Any ideas?All ideas welcome!Or is it a deadend?THANKS&nbsp;

lbendlin · Answer

It's the same idea in Power BI.&nbsp; You create a measure that uses the same rules as your color coding.
&nbsp;
Please provide sample data that covers your issue or question completely. https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216 Please show the expected outcome based on the sample data you provided. https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

sportily · Answer

Hi,Thanks for the reply.Nice to know if may be possible. Although I'm not sure I know what to do from your description.For the conditional formatting I apply this using the tool in each value displayed in the table.I've mocked this up in Excel showing the COUNTIF based on the AVERAGE figues displayed in a table, filtered from a dataset.Here :&nbsp;shorturl.at/aftU4&nbsp;Thanks so much

lbendlin · Answer

Like this ?

Or do you want to count this across columns PLAN and EAT?&nbsp; In that case you need to unpivot your data first.

sportily · Answer

Thanks.Yes,&nbsp; The measure would be COUNTIF( Average of [Plan] and Average of [Eat],&nbsp; &nbsp;&gt;-4 )&nbsp;&nbsp;So it would deliver either 0, 1, 2 as the answers.&nbsp;In your example Mark would be a 0 and Simon would be a 1.&nbsp;The colour coding is achieved using conditional formatting and works fine currently. But I didnt know if there was a way to COUNTIF( cells in the row are background colour green ) as an alternative to counting the averages &gt;-4.&nbsp;Thanks for your interest.Chris

lbendlin · Answer

You cannot count the background colors per se, but as I have shown above you can have an explicit measure that creates the background color, and another parallel measure with the same logic that counts the occurrences.
&nbsp;
Please provide sample data that covers more of your scenarios, and indicate the expected outcome.

Forum Discussion

COUNTIF based on Averages in displayed table

5 Replies

Related content

Index Countif

Average and Moving Average

Weekly Average

Accumulated average

Countifs