Forum Discussion

blaj's avatar
blaj
Helper I
1 year ago

How do you color code a calculation group using field value?

If you have a calculation group in a matrix that calculates % change for over 20 selected measures, how do you color code % change based on field value without getting a numeric/date error?

 

Any way to color code directly in the calculation group item using DAX?

General Comment
Need Help
Tips and Tricks

2 Replies

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    1 year ago

    Hi blaj , give this a try, and if you encounter any issues, let me know.

    Create a Color Coding Measure

     

    ColorCode = 
SWITCH(
    TRUE(),
    [% Change] > 0.1, "Green",
    [% Change] < -0.1, "Red",
    "Black"
)

     

     

    Modify the Calculation Group Item

     

    FORMAT(
    [% Change],
    SWITCH(
        TRUE(),
        [% Change] > 0.1, "<span style='color:green'>" & FORMAT([% Change], "0.00%") & "</span>",
        [% Change] < -0.1, "<span style='color:red'>" & FORMAT([% Change], "0.00%") & "</span>",
        "<span style='color:black'>" & FORMAT([% Change], "0.00%") & "</span>"
    )
)

     

     

    Did I answer your question?  If so, please mark my post as the solution!✔️
    Your Kudos are much appreciated!  Proud to be a Responsive Resident!

  • blaj's avatar
    blaj
    Helper I
    1 year ago

    I tried this, but it didn't work:

     

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

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


var calc = 
    DIVIDE( lastperiod - previousperiod, previousperiod)

var result = 
    FORMAT(
    calc,
    SWITCH(
        TRUE(),
        calc > 0.05, "<span style='color:green'>" & FORMAT(calc, "0.00%") & "</span>",
        calc < 0.05, "<span style='color:red'>" & FORMAT(calc, "0.00%") & "</span>",
        "<span style='color:yellow'>" & FORMAT(calc, "0.00%") & "</span>"
    )
)

RETURN
    result


    The %Change is the Calcualtion Group itself, not a single measure. There are over 20 DAX measures which I need to calculate 1,3,6,12 month % change over each of them using a calculation group, then color code with field value where > 0.05 is green, < 0.05 is red. Please advise. Thank so much.