CODEWITHSUNDEEP
powerbiconditional-formatting
BlazarBlast
BlazarBlast

Reputation: 31

Power BI - Conditional Formatting per Row in Matrix Visual

I'm trying to use conditional formatting for the colours of a matrix. The goal is that for each row the colour of the cell will depend on it being above or below the average for that particular row.

I have managed to produce the following matrix using this DAX measure as the conditional formatting:

Conditional Formatting =  
VAR SelectedMonth = CALCULATE(SUMX(Reviews_Table,Reviews_Table[Number_of_Reviews])) 
VAR AveragePerCountry = [Average Reviews] 
RETURN 
SWITCH(
    TRUE(),
    SelectedMonth >= 1.5*AveragePerCountry, "#008651",
    AND(SelectedMonth < 1.5*AveragePerCountry, SelectedMonth >= AveragePerCountry), "#82ac2b",
    AND(SelectedMonth < AveragePerCountry, SelectedMonth >= 0.5*AveragePerCountry), "#ff8100",
    SelectedMonth < 0.5*AveragePerCountry, "#FE2828" )

where [Average Reviews] measure has been defined as

Average Reviews = 
VAR SUMFILTER =
    CALCULATE (
        SUM ( Reviews_Table[Number_of_Reviews] ),
        ALLSELECTED( 'Reviews_Table' )
    )

VAR COUNTFILTER =
    CALCULATE (
        DISTINCTCOUNT  ( Reviews_Table[Number_of_Reviews] ),
        ALLSELECTED( 'Reviews_Table' )
    )
RETURN
    DIVIDE ( SUMFILTER, COUNTFILTER )

However, as it stands, this conditional formatting seems to compare each value to the total average over all rows. Is it possible to obtain the average for each row (in this case "Market") and use that as conditional formatting? Hopefully the answer will respect filters (I have a slicer that changes the time interval, and the average should be for that specific interval).

All help would be appreciated!

PS: Just as additional info Reviews_Table has the following columns: Month, Market, Retailer (not depicted in the matrix but accessible by pressing "+") and Number_of_Reviews.

Upvotes: 3

Views: 2383

Answers (1)

Luke_0
Luke_0

Reputation: 855

The idea is to identify the row context and the column context, and to only remove the column context when evaluating the row average while retaining the row context. Here, your use of ALLSELECTED() actually strips both row and column context. I am going to assume Month is your column dimension here, but substitute that for whatever you need.

The first step is to create a separate dimension tables for the time dimension, if you haven't already.

Calendar = CALENDARAUTO()

Then create a month column to summarize 'Calendar' by, but connect 'Calendar' to 'Reviews_Table' by 'Calendar'[Date] = 'Reviews_Table'[Month] (assuming 'Reviews_Table'[Month] is a date-type).

Then, replace the ALLSELECTED('Reviews_Table') function in your measure with ALLSELECTED('Calendar'). This will keep the market/retailer context when evaluating the average, but it will expand the time context to include all time periods in the visual (still respecting slicers/filters outside the visual).

Supplementary advice:

I noted you used two variables with the same CALCULATE() filters in each. You could combine these into one expression for simplicity.

Average Reviews = 
CALCULATE (
    DIVIDE( 
        SUM ( Reviews_Table[Number_of_Reviews] ),
        DISTINCTCOUNT  ( Reviews_Table[Number_of_Reviews] )
    ),
    ALLSELECTED( 'Reviews_Table' )
)

I also noted you are defining the average as DIVIDE( SUM(), DISTINCTCOUNT() ). This will evaluate retailers with the same number of reviews/month/market a single datum, inflating your average. If this is intentional, disregard, but you may want to either substitute this for a simple AVERAGE() function or be more explicit about dividing by the number of selected dimension members. Your decision here will also impact how missing data points are treated in the average.

Upvotes: 0

Related Questions