Forum Discussion

corbs's avatar
corbs
Frequent Visitor
1 year ago

MAX OF A MEASURE IN CONDITIONAL FORMATTING

I need help.

I have a metric that is a %, simple one: 

 

YTD Metric=
var numerator = CALCULATE(SUM(fact[METRIC_VALUE]),fact[METRIC_TYPE]="numerator")
var denominator = CALCULATE(SUM(fact[METRIC_VALUE]),fact[METRIC_TYPE]="denominator")
return
DIVIDE(numerator,denominator)
 
This metric is displayed in a matrix:
I have month in rows (coming from Calendar table), year (from calendar) and jobtitle (from fact table) as columns.
I need to highlight the highest value per row, per job title, probably with conditional formatting, as shown below:
 
 

 

To achieve that, I am trying to get the MAXX of the measure, grouped by year, month and job title. 

I've tried different ways, but cannot return any result. Can someone help me with that? I am open to new approaches.

 

MaxValueForGroup =
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Month])
VAR CurrentJobTitle = SELECTEDVALUE('Fact'[Job Title])
RETURN
CALCULATE(
MAXX(
VALUES('Fact'),
[YTD Measure]
),
'Calendar'[Month] = CurrentMonth,
'Fact'[Job Title] = CurrentJobTitle
)

 

this was a separate approach, I had issues with ALLEXEPT (query exceed resources) and then I tried KEEPFITLERS. But same result.

MaxValueForGroup =
CALCULATE(
MAXX(
VALUES('Fact'),
[YTD Measure]
),
ALLEXCEPT('Calensar', 'Calendar'[Month]),
ALLEXCEPT('Fact', 'Fact'[Job Title])
)

 

 

General Comment
Interesting Links
Need Help
Show and Tell

6 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi corbs 

     

    Please try something like below:

    MAXX Year = 
MAXX (
    ADDCOLUMNS (
        SUMMARIZE ( ALL ( Dates[Year] ), Dates[Year] ),
        "@rev", [Total Revenue]
    ),
    [@rev]
)

     

     

    • corbs's avatar
      corbs
      Frequent Visitor
      1 year ago

      Thank you so much.

      I got it make it work using both solutions: create the maxx as you suggested and the flags as per DataNinja777 suggested.

      However, it's not dynamic as per the user selection of year. Is there a way to do that?

      This is when all years are selected in the slicer - working perfectly! :

       

       

      If user select for example 2020 - 2023: 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi corbs , hello DataNinja777 and danextian, thank you for your prompt reply!


        Use the following measure to dynamic calcualte:

        MAXX Year = 
MAXX(
    ADDCOLUMNS(
        SUMMARIZE(
            ALLSELECTED('Calendar'[Year]),  
            'Calendar'[Year]  
        ),
        "@rev", [YTD Metric]  
    ),
    [@rev]  
)

         Result:

        Best regards,

        Joyce

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

         

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    Hi corbs ,

     

    To highlight the highest value for the YTD Metric in your matrix visual, you can use a combination of measures to calculate the maximum value dynamically and apply conditional formatting based on it. The first step is to create a measure that determines the maximum YTD Metric value for each combination of year, month, and job title. This requires ignoring the current cell's filter context while maintaining the grouping structure.

    Here’s a measure to calculate the maximum value for each group:

    MaxValueForGroup =
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Month])
VAR CurrentJobTitle = SELECTEDVALUE('Fact'[Job Title])
RETURN
CALCULATE(
    MAXX(
        SUMMARIZE(
            ALL('Fact'),
            'Calendar'[Year],
            'Calendar'[Month],
            'Fact'[Job Title],
            "YTDValue", [YTD Metric]
        ),
        [YTDValue]
    ),
    'Calendar'[Year] = CurrentYear,
    'Calendar'[Month] = CurrentMonth,
    'Fact'[Job Title] = CurrentJobTitle
)

    This measure uses SUMMARIZE to group the data by year, month, and job title, calculating the maximum YTD Metric within those groups. The ALL function ensures the calculation considers the entire dataset, ignoring the filter context of the matrix visual.

    Next, create a measure to determine whether the current cell's YTD Metric equals the maximum value for its group. This measure will be used for conditional formatting:

    Highlight Max =
IF(
    [YTD Metric] = [MaxValueForGroup],
    1,
    0
)

    This measure compares the YTD Metric for the current cell to the calculated maximum value for the group. If the values match, it returns 1; otherwise, it returns 0.

    To apply conditional formatting in the matrix visual, use the Highlight Max measure. Select the matrix visual, navigate to the YTD Metric field, and apply conditional formatting based on rules. Configure the rule so that a value of 1 corresponds to a specific background or font color (e.g., green for highlighting).

    This approach calculates the maximum dynamically, respects the required grouping, and ensures efficient performance. If your dataset is particularly large, additional optimizations, such as pre-aggregating data in the fact table, may improve performance.

     

    Best regards,