Forum Discussion
Conditional formatting using multiple calculation groups
I have a question. I have a matrix where I want to use two calculation groups: one for Time Intelligence and another that controls the selected measure. I also have a generic measure that I use in the matrix to ensure the calculation group works correctly.
Matrix:
Rows: Customer
Columns: Time Intelligence [periods] (This is a calculation group)
Values: generic_measure
The Time Intelligence calculation group has 5 calculation elements:
Current Period, Previous Month, Previous Year, Variation1, and Variation2
I’m using a Data Slicer where I apply the calculation group to the measures, which allows me to control the values in the matrix. However, when I try to apply conditional formatting, it applies to all columns or elements of the Time Intelligence group. I only want it to apply to the variations. How can I do this? I’ve watched videos, but those are for cases where I’m using only one measure. And I don’t want to create different pages to switch measures.
Drive the color from a dedicated measure that checks the current Time Intelligence calculation item and returns BLANK() for the ones you do not want to color. In a matrix the color measure is evaluated once per column, so returning BLANK() effectively turns formatting off for those columns while it still applies where you return a color.
Variation Color = VAR CurrentItem = SELECTEDVALUE ( 'Time Intelligence'[Name] ) VAR Val = [generic_measure] RETURN SWITCH ( TRUE(), CurrentItem IN { "Variation1", "Variation2" } && Val > 0, "#2E7D32", CurrentItem IN { "Variation1", "Variation2" } && Val < 0, "#C62828", BLANK() )Then on generic_measure open Conditional formatting, choose Background color or Font color, pick Format by: Field value and select Variation Color. Adjust the column name inside SELECTEDVALUE to match the actual column of your Time Intelligence calculation group. The second calculation group keeps working because the color measure references [generic_measure], which resolves under whatever that group dictates.
If it solved your issue, please mark it as the accepted solution and give it a kudos.
Best regards,
Shai Karmani
5 Replies
- Shai_Karmani
Super User
Drive the color from a dedicated measure that checks the current Time Intelligence calculation item and returns BLANK() for the ones you do not want to color. In a matrix the color measure is evaluated once per column, so returning BLANK() effectively turns formatting off for those columns while it still applies where you return a color.
Variation Color = VAR CurrentItem = SELECTEDVALUE ( 'Time Intelligence'[Name] ) VAR Val = [generic_measure] RETURN SWITCH ( TRUE(), CurrentItem IN { "Variation1", "Variation2" } && Val > 0, "#2E7D32", CurrentItem IN { "Variation1", "Variation2" } && Val < 0, "#C62828", BLANK() )Then on generic_measure open Conditional formatting, choose Background color or Font color, pick Format by: Field value and select Variation Color. Adjust the column name inside SELECTEDVALUE to match the actual column of your Time Intelligence calculation group. The second calculation group keeps working because the color measure references [generic_measure], which resolves under whatever that group dictates.
If it solved your issue, please mark it as the accepted solution and give it a kudos.
Best regards,
Shai Karmani - Kedar_Pande
FormatColor =
VAR CurrentItem = SELECTEDVALUE('Time Intelligence'[Time Intelligence])
RETURN
SWITCH(
CurrentItem,
"Variation1", IF([generic_measure] < 0, "#FFC7CE", "#C6EFCE"),
"Variation2", IF([generic_measure] < 0, "#FFC7CE", "#C6EFCE"),
BLANK()
)Then on generic_measure, go to conditional formatting > background color > format by Field value > pick FormatColor.
v-aatheequeCommunity Support
Hi GoresiDevBI
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.
- Ashish_Mathur
Hi,
Please share the download link of the PBI file. Kinldy show the problem there clearly.
- v-aatheeque
Hi GoresiDevBI
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.
