Forum Discussion

blaj's avatar
blaj
Icon for Helper I rankHelper I
2 years ago

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

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.

 

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      blaj
      Icon for Helper I rankHelper I

      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's avatar
        Anonymous
        Not applicable

        Hi blaj 

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

         

        Best Regards!

        Yolo Zhu

    • blaj's avatar
      blaj
      Icon for Helper I rankHelper I

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