How to Apply Conditional Formatting to a Matrix where Two Calculation Groups are applied

Hi somnath6309 

First of all, Bernat Agulló Roselló has written a great article on conditional formatting with calculation groups:

https://www.esbrina-ba.com/calculation-groups-and-conditional-formatting-yes-its-possible/

 

Apply(referring to the existing calculation groups as Metric Selection and TimeCalculations )

• Create an arbitrary placeholder measure for conditional formatting called ConditionalFormat say.
  It should return a text value to be selectable in the dialog box so set it to CONVERT ( BLANK(), STRING ) for instance..
• Create a Formatting calculation group with a calculation item that returns the required colour as text (my version shown below).
• Ensure that the calculation group precedence is Metric Selection < TimeCalculations < Formatting.
• Set up the visual with
  • TimeIntelligence in Columns
  • Any measure in Values (will be overridden)
  • Conditional formatting set to use the Field option based on the ConditionalFormat measure.
  • Apply at least a visual-level filter of Formatting[Formatting] = "Green Red".

I've attached a sample model using Contoso data to illustrate:

 

My calculation groups looks like this (TE3 DAX script):

----------------------------------------
-- Calculation Group: 'Metric Selection'
----------------------------------------
CALCULATIONGROUP 'Metric Selection'[Metric]    , Precedence = 1

    CALCULATIONITEM "Sales Amount" = [Sales Amount]
        , FormatString = "#,0;(#,0);-"
        , Ordinal = 0

    CALCULATIONITEM "Sales Qty" = [Total Quantity]
        , FormatString = "#,0;(#,0);-"
        , Ordinal = 1

    CALCULATIONITEM "TotalCost" = [Total Cost]
        , FormatString = "#,0;(#,0);-"
        , Ordinal = 2

    CALCULATIONITEM "Margin Pct" = [Margin %]
        , FormatString = "0.00%;(0.00%);-"
        , Ordinal = 3

----------------------------------------
-- Calculation Group: 'TimeCalculations'
----------------------------------------
CALCULATIONGROUP TimeCalculations[TimeIntelligence]    , Precedence = 2

    CALCULATIONITEM "CY" = SELECTEDMEASURE ()
        , FormatString = SELECTEDMEASUREFORMATSTRING ( )
        , Ordinal = 0

    CALCULATIONITEM "PY" = 
        CALCULATE (
            SELECTEDMEASURE ( ),
            SAMEPERIODLASTYEAR ( 'Date'[Date] )
        )
        , FormatString = SELECTEDMEASUREFORMATSTRING ( )
        , Ordinal = 1

    CALCULATIONITEM "YOY" = 
        VAR CY = SELECTEDMEASURE ( )
        VAR PY =
            CALCULATE (
                SELECTEDMEASURE ( ),
                SAMEPERIODLASTYEAR ( 'Date'[Date] )
            )
        VAR Result = CY - PY
        RETURN
            Result
        , FormatString = SELECTEDMEASUREFORMATSTRING ( )
        , Ordinal = 2

    CALCULATIONITEM "YOY Pct" = 
        IF (
            -- Don't calculate percentage change of a percentage :)
            NOT SELECTEDVALUE ( 'Metric Selection'[Metric] ) IN { "Margin Pct" },
            VAR CY = SELECTEDMEASURE ( )
            VAR PY =
                CALCULATE (
                    SELECTEDMEASURE ( ),
                    SAMEPERIODLASTYEAR ( 'Date'[Date] )
                )
            VAR Result = DIVIDE ( CY - PY, PY )
            RETURN
                Result
        )
        , FormatString = "0.00%;(0.00%);-"
        , Ordinal = 3

----------------------------------
-- Calculation Group: 'Formatting'
----------------------------------
CALCULATIONGROUP Formatting[Formatting]    , Precedence = 3

    CALCULATIONITEM "Green Red" = 
        IF (
            ISSELECTEDMEASURE ( [ConditionalFormat] ),
            IF (
                SELECTEDVALUE ( TimeCalculations[TimeIntelligence] ) IN { "YOY", "YOY Pct" },
                VAR MeasureValue = SELECTEDMEASURE ( )
                VAR Result =
                    CONVERT ( IF ( MeasureValue >= 0, "green", "red" ), STRING )
                RETURN
                    Result
            ),
            SELECTEDMEASURE ( )
        )

 

 

Are you able to adapt this method to your model/report?

Hi somnath6309 

Thanks for the follow-up questions 🙂

 

Firstly, I would recommend the set of articles on calculation groups found here:
https://www.sqlbi.com/calculation-groups/

 

01A.
SELECTEDMEASURE () returns a reference to the "current" measure being evaluated in the Power BI report itself.
This is generally a measure displayed in the visual, or a measure included elsewhere in a visual such as within conditional formatting or a visual-level filter.

The ultimate value returned by SELECTEDMEASURE () is the value of the measure (since a measure reference evaluates to a scalar value), not its name.

 

01B.
Actually, the CONVERT function isn't needed here. I forgot to remove it.
However, it (or an alternative) is needed in the measure

ConditionalFormat =
CONVERT ( BLANK (), STRING )

This ensures that the ConditionalFormat measure is recognised as having a text value so that it can be selected in the conditional formatting dialog box.

Another option would be

ConditionalFormat =
""

 

02
SELECTEDMEASUREFORMATSTRING () returns the format string of the current measure, that is the format string of the measure returned by SELECTEDMEASURE ().

The reason for using it is so that the underlying measure's format string is retained when applying a calculation item, so that numbers are formatted as per the original measure.

 

03
I created this with ScreenToGif. I recorded a snippet of interaction with the Power BI report, then saved as an animated GIF and inserted into the post as a picture.

https://www.screentogif.com/

 

Hope that helps!

Owen 🙂

You're welcome, and I'm glad you were able to mark it as a solution 🙂

 

01.

To ensure that the conditional formatting applies to row subtotals, make sure Apply to "Values and totals" is selected in the conditional formatting dialog box:

02.

Yes for PY grey, but no for PY in italics (as far as I know).

To change PY text colour to grey, change the "Green Red" calculation item expression to something like this:

IF (
    ISSELECTEDMEASURE ( [ConditionalFormat] ),
    VAR CurrentTimeCalc =
        SELECTEDVALUE ( TimeCalculations[TimeIntelligence] )
    RETURN
        SWITCH (
            TRUE ( ),
            CurrentTimeCalc IN { "YOY", "YOY Pct" },
                VAR MeasureValue = SELECTEDMEASURE ( )
                VAR Result = IF ( MeasureValue >= 0, "green", "red" )
                RETURN
                    Result,
            CurrentTimeCalc = "PY", "grey"
        ),
    SELECTEDMEASURE ( )
)

I have attached an updated PBIX with this change.

 

I'm not aware of any way of changing font style settings (bold, italic) for a single column of a matrix, whether the columns are different groupings of the same measure or different measures. You could look into custom visuals such as InfoRiver or Zebra BI Tables which have this kind of flexible formatting functionality (these are paid visuals for full functionality).

 

Kind regards,

Owen

Hi somnath6309 

It turns out that Microsoft has decided to restrict the direct file attachment function to Super Users (and probably admins or other groups).

 

The next best option is to share a link to a file in cloud storage (OneDrive, Google Drive etc).

 

This is what I see when authoring a reply: