Forum Discussion

corange's avatar
corange
Post Patron
6 years ago
Solved

Conditional Formatting - Less / Greater than

Hi POWER BI Team,    I need some help with a project. I have looked around but could not find an answer and the conditonal formatting function in the format section doesn't do the trick for me.   ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi corange 

    I have a solution below though it is a little different from your final expected result.

    week is a column

    week = IF(TODAY()=[date],"today",FORMAT([date],"dddd"))

    [Measure] is a measure

    Measure =
VAR sl =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[client] = MAX ( 'Table'[client] )
                && 'Table'[week] <> "today"
        )
    )
VAR t =
    SUM ( 'Table'[value] )
RETURN
    IF (
        MAX ( [week] ) = "today",
        SWITCH (
            TRUE (),
            t > sl
                || (
                    t <> BLANK ()
                        && sl = BLANK ()
                ), 1,
            t = sl
                || (
                    t = BLANK ()
                        && sl = BLANK ()
                ), 0,
            t < sl
                || (
                    t = BLANK ()
                        && sl <> BLANK ()
                ), -1
        )
    )

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi corange 

I have a solution below though it is a little different from your final expected result.

week is a column

week = IF(TODAY()=[date],"today",FORMAT([date],"dddd"))

[Measure] is a measure

Measure =
VAR sl =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[client] = MAX ( 'Table'[client] )
                && 'Table'[week] <> "today"
        )
    )
VAR t =
    SUM ( 'Table'[value] )
RETURN
    IF (
        MAX ( [week] ) = "today",
        SWITCH (
            TRUE (),
            t > sl
                || (
                    t <> BLANK ()
                        && sl = BLANK ()
                ), 1,
            t = sl
                || (
                    t = BLANK ()
                        && sl = BLANK ()
                ), 0,
            t < sl
                || (
                    t = BLANK ()
                        && sl <> BLANK ()
                ), -1
        )
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

corange's avatar
corange
Post Patron
6 years ago

Hi everyone, 

 

Following my first post, I would like to create another aggregation but not sure what to do about it. 

 

I would like to see the difference and then the percentage difference between Today and the other day of the week. So when I select Monday and today it gives me the difference between Today job and Monday Jobs and so on for the rest of the week.

 

Can anyone help me on this? 

 

Thank you.