Conditional formatting with Calculation Groups in PowerBI Matrix visual

This is going to be the first of 2 articles explaining how to introduce conditional formatting and sorting when using calculation groups in Matrix visual. It would be great if conditional formatting and sorting could be applied to a calculation item, but until this happens, here is a workaround.

Link to the second part of series where we explain how to introduce sorting with calculation groups: Link

Below, you can test a working example in the embedded report, and if you wish to follow along, you can download .pbix file here.


If you haven’t started using calculation groups yet, you should! They are the best new feature in the PowerBI data model in years. They make our data modeling lives easier by reproducing the same filter context for any measure we provide as input and act as a “placeholder” for measures.

Calculation groups can be also used for more advanced modeling, but that is out of the scope of this article.

Although great, they currently do have some shortcomings that I hope will be implemented in future releases. One of the features currently missing is the possibility to conditional format a single item from the calculation group.
To demonstrate what is missing, I’ve created a simple data model as an extract from Contoso DB.

The Model


The data model consists of 2 tables (DimDate and FactTable), and a single calculation group called TimeIntelligence.

We won’t go into an explanation on how to create calculation groups/items and how to format them since there are many articles and videos already covering those topics. Calculation items were created in the same fashion using the time intelligence patterns from SQLBI guys. You can follow this link to check more about time intelligence patterns. We won’t explain the code of each calculated item since it would take too much effort while these guys already made an awesome job explaining them in detail.

When we invoke the calculation group with all the items onto our PowerBI canvas, we receive the following table. We used Matrix visual for demonstration.

As we can see, it’s pretty hard to distinguish positive from negative absolute and relative changes.


If we want to conditionally format this table, we are stuck with a single value that can be formatted, and that is a Measure used in the calculation.

Currently, there is no option to conditionally format just a single CalculationItem (at least I haven’t found one), but there is a trick to simulate that behavior. 

Conditional Measures


The trick is to use a dedicated Measure that will be provided as a basis for conditional formatting of the matrix. In that measure, we can define to which columns conditional formatting should apply.

To demonstrate this, we will create 2 additional Measures, called Conditional% and ConditionalAbs.

Conditional% =
IF (
    SELECTEDVALUE ( TimeIntelligence[Periods] ) IN { "QTD%Chng", "YTD%Chng" },
    [TotalQuantity],
    BLANK ()
)

ConditionalAbs =
IF (
    SELECTEDVALUE ( TimeIntelligence[Periods] )
        IN { "QTD TY vs LY", "YTD TY vs LY" },
    [TotalQuantity],
    BLANK ()
)


The idea is to create a measure that shows value only for the calculation items we provide in the curly brackets. For other measures we want them to evaluate to blank, meaning that conditional formatting will ignore them.

Next, we need to go to the conditional formatting options of the Matrix and select to apply those measures as a basis for conditions.

We need to go to the advanced controls of the condition we wish to alter. In the advanced options window, we need to change the “Based on field” value from TotalQuantity measure to our newly created ConditionalAbs Measure.


We do the same procedure with the Icons conditional formatting option, but this time we select Conditional% as a base measure.


After we confirm, we receive a nicely formatted Matrix.



Bonus Tip


You can use your calculation group column as a filter for the matrix. Conditional formatting is preserved and is being activated when you select those fields in the slicer.

Hope you find this trick useful. We will use a similar technique to create Matrix sorting, which will be shown in the following article.

If you have any questions, please post them below the article.

Table of Content

Table of Content

LEARN POWER QUERY

Improve your PowerQuery skills with
Exceed Academy

COMMENTS

Subscribe
Notify of
guest
30 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

Held PowerApps training for Žito d.d.

Last week we went to Osijek to hold a PowerApps training for Žito d.d. The training took place at the client’s office, lasting 2 days, with a total duration of 8 hours. The participants of the training have been using PowerApps in their business for some time, but they wanted...

Read more

Održana PowerApps edukacija za Žito d.d.

Prošli tjedan svratili smo do Osijeka kako bismo održali PowerApps edukaciju za Žito d.d. Edukacija se održala u 2 dana kod klijenta u uredu, u sveukupnom trajanju od 8 sati. Polaznici edukacije već neko vrijeme koriste PowerApps u svom poslovanju, no željeli su usavršiti svoje vještine u samostalnoj izradi aplikacija,...

Read more

This year’s first PowerApps training for TÜV NORD Adriatic

Last week we held this year’s first PowerApps training for our long-term clients from the TÜV NORD Adriatic company. It was a full-day education lasting 6 hours, led by our PowerApps expert and developer Kristian! The PowerApps platform is intended for the creation of customized business applications and enables the...

Read more

Prva ovogodišnja PowerApps edukacija za TÜV NORD Adriatic

Prošli tjedan održali smo prvu ovogodišnju PowerApps edukaciju za naše dugogodišnje klijente iz TÜV NORD Adriatic kompanije. Bila je to cjelodnevna edukacija u trajanju od 6 sati, koju je vodio naš PowerApps stručnjak i developer Kristian! PowerApps platforma namijenjena je izradi prilagođenih poslovnih aplikacija i omogućava brzi razvoj aplikacija koje...

Read more
30
0
Would love your thoughts, please comment.x
()
x
Scroll to Top