How do I color code Calculation Groups with over 20+ Measures in Matrix?

[blaj]

I have created 20+ DAX measures. I also used a calculation group to quickly measure percentage change accross 1,3,6,12 month periods in a matrix table.

How would I be able to create a conditional color-coding rule in the matrix table for all of these at the same time and/or without having to  create 4 change measures per each measure just to use it as a conditional formatting field ?

Consider the following:

• The 20+ DAX measures do not measure change. The claculation group does.
  • Normally, I would use the field value option for contitional formatting, but that doesn;t work in this case because the field value does not allow calculation group items. The conditional formatting applies to the 1,3,6,12 month chnage, not the DAX measure itself.

I created the concept in Excel to conceptualize this problem. Conditional form applies to all measures with a 1,3,6,12 month change, where > 5% change is green, <= 5% is red.

Here's an example of the 1-Month chnage claculation item. Same concept for 3,6,12 months.

Calculation Group Trailing Chnage Percent:

Item: 1 Month

Item: 3 Months

Item: 6 Months

Item: 12 Months

1 Month = 
var previousperiod =
    CALCULATE(
        SELECTEDMEASURE(),
        DATETABLE[Month Offset] = -2,
        REMOVEFILTERS(DATEPERIOD)
    )

var lastperiod =
    CALCULATE(
        SELECTEDMEASURE(),
        DATETABLE[Month Offset] = -1,
        REMOVEFILTERS(DATEPERIOD)
    )

var result = 
   DIVIDE( lastperiod - previousperiod, previousperiod)

RETURN
    result


3 Months = 

var previousperiod =
    CALCULATE(
        SELECTEDMEASURE(),
        DATETABLE[Month Offset] >= -6 && DATETABLE[Month Offset] <= -4,
        REMOVEFILTERS(DATEPERIOD)
    )

var lastperiod =
    CALCULATE(
        SELECTEDMEASURE(),
        DATETABLE[Month Offset] >= -3 && DATETABLE[Month Offset] <= -1,
        REMOVEFILTERS(DATEPERIOD)
    )


var result = 
    DIVIDE( lastperiod - previousperiod, previousperiod)

RETURN
    result

Please help! Thanks in advance.

[Anonymous]

Hi @blaj 

You can create a new table, put the names of the measures to the table. such as the following.

Then crearte a new measure.

Measure =
SWITCH (
    SELECTEDVALUE ( 'Table'[Column1] ),
    "Sales", [Sales],
    "Orders", [Orders],
    "Sales/Customer", [Sales/Customer],
    "Sales/Order", [Sales/Order]
)

Then put the Column1 of the table to the row and put the measure to the value in matrix.

Then create a new measure2 to format the measure

Measure 2=
SWITCH ( TRUE (), [Measure] <= 0.05, "red", [Measure] > 0.05, "green" )

Then put the measure2 to the conditional formatting of measure.

Then it can work.

Best Regards!

Yolo Zhu

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

[blaj]

@Anonymous Hello. How did you do your calculation group? Did you use [measure] or SELECTEDMEASURE()? I think that may help debug the issue. Thanks.

[blaj]

Thank you for your response. I am good up till the field value formatting. This is the error I get. 

Also, what if some of these measures need to be color coded green if a negative number, and red if positive?

For example, if Churn Rate 1-month change is negative then color code green, if 3-month change is positive then color code red. That's becuase this is a loss measure.

Another consideration is that the Calculation Group is actually a Parameter over 2 calculation groups. One calculation group is the absolute amount change, the other percentage change. The paremeter serves a s a toggle to view abosule change amount or percentage change amount.

[Anonymous]

Hi @blaj 

Please check what your measure type is and if you have a text type in your measure?

Best Regards!

Yolo Zhu

[blaj]

No, I just posted an image of the dax code. It is the same as you provided.

[Anonymous]

Hi @blaj 

In my example, this measure works fine when put into conditional formatting, can you put this measure directly into the visual to see the result of the run, but not in conditional formatting

Best Regards!

Yolo Zhu

[blaj]

Hi @Anonymous 

The field value doesn;t work becuase the calculation group is text type. Please advise.

Here are my steps:

1. Create measures

2. Create % Change calculation group items (4)

3. Create Measure 1 using SWITCH to create a new table with measure name column tied to the measure

4. Create Measure 2 with conditional format color code

5. Drop into Matrix: Column 1 into rows, Calculation Group into Columns, Measure 1 into values

6. Turn on conditional format as a a field using measure 2

Result: