Forum Discussion
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
- AnonymousNot 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
Helper 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.
- AnonymousNot 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
Helper 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.