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
amconnel
Resolver II
Resolver II

Matrix Conditional Formatting with Slicer or Filter

Hi, 

 

I have created the following measure to achieve conditional formatting so that the maximum in each row of the matrix will be highlighted green, excluding the quarters that have not been completed (Q3 and Q4 of year 21) and the year total that has not been completed (FY21) and the minimum of each row will be highlighted red with the same exclusions. This formula is completely functional. 

amconnel_0-1619462228685.png

 

However, I am needing to add a slicer so that I am ableto view groups individually and as a whole. I have three groups (Group A, Group B, and Group C). I have added this group metric to a slicer, and the slicer is functional and shows the correct values for each group as well as the correct conditional formatting. The issue I am having is that when I choose to select all of the groups so that I may look at them as a whole, the conditional formatting does not work. What adjustments can be made to my formula so that this formatting will function when looking at the full picture?

This is what the matrix looks like when viewing all groups as a whole: (data has been altered due to sensitivity - please do not ask to see the data source.) Rows: Quarter  | Columns: Year | Values: Sales

amconnel_1-1619462303251.png

This is what the matrix looks like when you are viewing a group individually. This is my goal end product, however, I need this translate when look at all groups at once:

amconnel_2-1619462439245.png

 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @amconnel ,

 

Measures are based on context, and when you do a grouping you are picking up the SUM of the sales amount and not the SELECTEDVALUE so when you are calculating the values for each line you are not getting the correct values since you are comparing values around 2K 3 K with 17K to 20K.

 

Believe that you need to redo your measure in order to pick up the SUM of sales and then get the MAXX and MINXX of the values.

 

The only question here is the table you need to use on the MAXX and MINX

 

Sales format new =
VAR Total =
    CALCULATE (
        SUM ( Segments[Sales] ),
        FILTER (
            Segments,
            ( Segments[Year] <> "21"
                || Segments[Quarter] <> "Q3" )
                && ( Segments[Year] <> "21"
                || Segments[Quarter] <> "Q4" )
                && ( Segments[Year] <> "21"
                || Segments[Quarter] <> "FY" )
        )
    )
VAR MAXTotal =
    MAXX (
        SUMMARIZE (
            FILTER (
                Segments,
                ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "Q3" )
                    && ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "Q4" )
                    && ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "FY" )
            ),
            Segments[Quarter],
            "@TotalSales", SUM ( Segments[Sales] )
        ),
        [@TotalSales]
    )
VAR MINTotal =
    MINX (
        SUMMARIZE (
            FILTER (
                Segments,
                ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "Q3" )
                    && ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "Q4" )
                    && ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "FY" )
            ),
            Segments[Quarter],
            "@TotalSales", SUM ( Segments[Sales] )
        ),
        [@TotalSales]
    )
RETURN
    SWITCH ( TRUE (), Total = MAXTotal, "#007A00", Total = MINTotal, "#FF0000" )

 

I have tried to make a summarization on the measure above but this may not give correct values since the context of the measure needs to be in this case the quarters but depending on the context within the matrix may need to be added more columns.

 

Without knowing the way the data is setup is difficult to give you further assistance, but the problem is for sure in the aggregation that is not being considered.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

Hi @amconnel ,

 

Measures are based on context, and when you do a grouping you are picking up the SUM of the sales amount and not the SELECTEDVALUE so when you are calculating the values for each line you are not getting the correct values since you are comparing values around 2K 3 K with 17K to 20K.

 

Believe that you need to redo your measure in order to pick up the SUM of sales and then get the MAXX and MINXX of the values.

 

The only question here is the table you need to use on the MAXX and MINX

 

Sales format new =
VAR Total =
    CALCULATE (
        SUM ( Segments[Sales] ),
        FILTER (
            Segments,
            ( Segments[Year] <> "21"
                || Segments[Quarter] <> "Q3" )
                && ( Segments[Year] <> "21"
                || Segments[Quarter] <> "Q4" )
                && ( Segments[Year] <> "21"
                || Segments[Quarter] <> "FY" )
        )
    )
VAR MAXTotal =
    MAXX (
        SUMMARIZE (
            FILTER (
                Segments,
                ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "Q3" )
                    && ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "Q4" )
                    && ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "FY" )
            ),
            Segments[Quarter],
            "@TotalSales", SUM ( Segments[Sales] )
        ),
        [@TotalSales]
    )
VAR MINTotal =
    MINX (
        SUMMARIZE (
            FILTER (
                Segments,
                ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "Q3" )
                    && ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "Q4" )
                    && ( Segments[Year] <> "21"
                    || Segments[Quarter] <> "FY" )
            ),
            Segments[Quarter],
            "@TotalSales", SUM ( Segments[Sales] )
        ),
        [@TotalSales]
    )
RETURN
    SWITCH ( TRUE (), Total = MAXTotal, "#007A00", Total = MINTotal, "#FF0000" )

 

I have tried to make a summarization on the measure above but this may not give correct values since the context of the measure needs to be in this case the quarters but depending on the context within the matrix may need to be added more columns.

 

Without knowing the way the data is setup is difficult to give you further assistance, but the problem is for sure in the aggregation that is not being considered.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Could you specify what your "@TotalSales" should be referencing? 

Hi @amconnel 

 

The "@TotalSales" must reference the sum of the sales in that way you can calculate the maximum and minimum at each point of your matrix.

 

This part of the calculation is use to aggregate the values is a "virtual column" created with the sum of your values.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.