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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sachintha
Advocate I
Advocate I

Conditionally format a table based on average of dataset, but adhering to slicer selections

I have a simple CSV data set such as this.

 

 

ID,MainCategory,SubCategory,Type,Value
1,E,E1,Demo,5
2,N,N3,Install,2
3,E,E1,Demo,4
4,E,E2,Install,7
5,D,D1,Install,3
6,S,S2,PM,4
7,N,N2,Install,7
8,N,N2,Demo,1
9,E,E2,Demo,2
10,D,D2,Install,6
11,D,D3,PM,4
12,S,S1,PM,8
13,N,N1,Install,5
14,S,S3,Install,8
15,S,S1,Demo,9
16,E,E3,Demo,5
17,N,N2,Install,3
18,E,E2,PM,6
19,D,D2,PM,6
20,N,N3,Demo,6
21,S,S2,Demo,7
22,E,E3,Install,2
23,S,S1,Install,4
24,S,S2,PM,8
25,D,D1,Install,5

 

 

In my Power BI Desktop, I'd like to load this into a table, and conditionally format the Value column based on whether the value in each row is greater than or less than the average for the currently selected data set.

For instance, the average of Value considering the entire table is 5.08, so if there are no filters applied (as in, all my slicers are set to select nothing), I'd like all rows whose Value is 6 or more to be background colored in one color, and the others in another color. For this, I created two measures like so:

  1. AvgOfVal = DIVIDE( SUM(G2G[Value]), COUNTA(G2G[ID]) )
  2. BGColor = IF(SUM(G2G[Value]) > [AvgOfVal], "Light Pink", "Light Blue")

Then I tried to apply the BGColor measure for conditionally formatting the background, but this doesn't work as expected, and instead produces the result below.

 

AvgAll.png

 

I realize that this is due to the fact that the measure is calculated per row, so when conditional formatting is applied, as seen in the AvgOfVal column in the table, it calculates average per row instead of for the entire data set. How can I calculate a measure that takes into account the entire data set (considering slicers), and do the conditional formatting as I need.

 

Please keep in mind that if a user were to select a slicer filter (say, MainCategory = D), then I want the conditional formatting to reflect this. So in this case, given that AvgOfVal = 4.80 for MainCategory = D entries, I'd like all rows whose Value >= 5 to be in one color, and others in another color.

 

AvgAll2.png

1 ACCEPTED SOLUTION
johncolley
Solution Sage
Solution Sage

Hi @Sachintha , Update your average measure to the below:

 

AvgOfVal = CALCULATE(AVERAGE(Sheet1[Value]), ALLSELECTED())

If this answer solves your problem please mark it as a solution!

 

View solution in original post

1 REPLY 1
johncolley
Solution Sage
Solution Sage

Hi @Sachintha , Update your average measure to the below:

 

AvgOfVal = CALCULATE(AVERAGE(Sheet1[Value]), ALLSELECTED())

If this answer solves your problem please mark it as a solution!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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