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

[somnath6309]

Hi,

In the below matrix visual I put Sales Amount measure in values area, in row area I put Calendar Year and Months i.e. Calendar Hierarchy. I created Time Calculations  Calculation group having column name TimeIntelligence which has calculation items like CY, PY, YOY, YOY Pct.  Following is the screen shot for reference. The precedence is 4 (highest) 

Now my goal is to show CY and PY Vales of Sales in BLACK (regular color) as the same can not be less than zero. However, YOY and YOY Pct are to shown in Red and Green for Negative and Positive values respectively. I used the following measure :

and used the same to do conditional formatting like below :

and every thing is working fine as desired :

However, I had created anothere calculation Group named Calculation group 2 having column name Metric with calculation items Sales Amount, Sales Qty, Total Cost  and MarginPct as shown below :

all the calculation items are measures :

I created a slicer and put the 'Calculation Group2'[Metirc] to the Field area of the Slicer :

Now the user can choose the items to be displayed in Matrix value area. However, when the "Sales Amount " is selected from the slicer the figures in CY and PY column got Green color. Obviously I put some conditional formatting as shown below screen shot:

the measure named "FormatSales" is as follows:

however, the requirement is : 

when Sales Amt , Sales Qty, Total Cost are selected by the user from the slicer, figures in CY and PY column should have regular black color, however, values in YOY and YOY Pct columns should be displayed in Red ( for Negative value) or Green ( Positive value).

The report should look like the following :

Please let me know what needs to be done to get the above result.

Thanks and Regards,

Somnath6309

[OwenAuger · Accepted solution]

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?

[OwenAuger · Accepted solution]

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 🙂

[OwenAuger · Accepted solution]

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

[somnath6309]

Hi, @OwenAuger 

can you pls let me know that how did you attached the PBIX file in the message ? The reference screen shot attached below:

Regards,

Somnath

[OwenAuger · Accepted solution]

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:

[somnath6309]

mentioning @OwenAuger : dear Owen, waiting for your reponse. Thanks in Advance. 

[OwenAuger · Accepted solution]

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 🙂

[somnath6309]

Hi, @OwenAuger 

Thanks for the help. I have marked it as solution. 

However, there are some other queries regarding this topic. Those are as follows:

01. Is it possible to apply the existing conditional formatting to Row Subtotals ? Pls see the screen shot below: 

Can we make the Row Subtotals for YOY and YOY Pct RED ( For negative value) or GREEN ( For positive value ) ? 

02. Is it possible to make the figures of PY Italics and to have the font color Grey ? I am pointing out the following highlighted column :

Regards,

Somnath

[OwenAuger · Accepted solution]

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

[OwenAuger · Accepted solution]

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?

[somnath6309]

Hi Owen Auger,

Thanks for your help. The solution is very very helpful. Howver, I have some queries regarding the same and those are as follows:

01A. Regarding the Calculation Item : "GreenRed"

following is your code for the same:

VAR MeasureValue = SELECTEDMEASURE()

Can you pls let me know which measure we are looking at here ? For example, if user selects Sales Amt, for YOY, the same is a Subtraction of Two measures i.e. Sales CY - Sales PY. 

However, is it correct that Subtraction of Two measures is also a Measure and we are pointing out that measure ?

Secondly, in variable MeasureValue, are we storing the Measure name or the value of the same ? 

01B.  The following line item in the code: 

VAR Result =
                    CONVERT ( IF ( MeasureValue >= 0, "green", "red" ), STRING )

as per my knowledge, "green", "red" are already string. Hence, can you pls elaborate on using of CONVERT function here ?

02. the use of SELECTEDMEASUREFORMATSTRING():

I request you to pls elaborate on the usage of SELECTEDMEASUREFORMATSTRING() in the folloiwng codes. Actually the same is not known to me. 

03. Lastly, how to create such animated picture which you have used at the end of your reply ? I am asking regarding this one:

Regards,

Somnath