Search code examples
powerbipowerbi-desktopconditional-formattingslicers

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.

enter image description here

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.

enter image description here

Solution

  • I realize that this is due to the fact that the measure is calculated per row

    Yes. The key is understanding how that happens. When the measure is calculated a "context transition" happens and the current row is added to the filter context.

    So what you want is a calculation that removes the row filter that was added in the context transition. So you need ALLSELECTED(), which does precisely that. eg

    AvgOvVAl = CALCULATE( AVERAGE('data'[Value]), ALLSELECTED() )

    Removing the "innermost" filter which in this case is the filter on the row, but leaving all other filters, ie filters added on the report, page, visual, or filters coming from interactions with other visuals like slicers.