Need to use an If/Else statement to apply formatting based on 2 rules

[shanerolle]

Hi everyone,

My current issue is that I am attempting to format a matrix based on a percentage of a value compared to another. Currently it is comparing calculated expenses against a budget and based on the two numbers, coloring a tile red if it is over budget, yellow between 90 and 100% of budget, or green if it is below 90% of the budget. That works when I am comparing anything being spent, but the problem is certain accounts in this same matrix are sales accounts, and therefore have money coming in which is displayed as a negative. The way it is currently calculated (the formulas are shown at the bottom of this post), when I have two numbers, such as -$1,337 of sales (so I profited $1337) but the budget, which is more of a target in this situation, is $-690, it shows the sales tile as red, indicating that it is bad, but realistically, having sales as higher than my target is good in this situation. This situation only applies to any account number that begins with a 5 as all the sales accounts begin with 5, and the accounts are currently the rows in the matrix. So for example any account that doesn't begin with a 5, I want my current formatting, but for any account beginning with a 5, I want to almost invert the formatting in a way.

My idea was to use an If/Else statement in the "Condition" listed below, so IF the account starts with a 5, then return a 1 if it is above the budget(target) (so green), a 2 if it is between .9 and 1 (yellow), and a 3 if it is below .9 (red), ELSE use the current condition listed below

My thought was that this will use the inverted formatting for all of the accounts starting with 5, but the normal formatting for everything else still.

If you need any more information or examples please let me know, I was not sure exactly how to explain what I am doing so I hope it makes sense. If I am doing this the wrong way please let me know, I tried using LEFT to make it work based on the first character but could not get it to work, so any advice is helpful.

Thank you,

Shane

The current measures/formatting are: 

Expense Measure = SUM(GLDT[Actual])

Condition = VAR Percent_ = [Expense Measure] / SUM (GLBDWithFYEMO[Budget]) RETURN SWITCH(TRUE (), Percent_ < .9, 1, Percent_ >= .9 && Percent_ <1, 2, Percent_ >= 1, 3)

Conditionally formatted the matrix so 1 = green, 2= yellow, 3=red based on the condition measure

[v-easonf-msft · Accepted solution]

Hi , @shanerolle 

Take a try to change your measure as below:

Condition =
VAR Percent_ =
    [Expense Measure] / SUM ( GLBDWithFYEMO[Budget] )
RETURN
    IF (
        LEFT ( SELECTEDVALUE ( 'Table'[Account] ) & "", 1 ) <> 5,
        SWITCH (
            TRUE (),
            Percent_ < .9, 1,
            Percent_ >= .9
                && Percent_ < 1, 2,
            Percent_ >= 1, 3
        ),
        SWITCH (
            TRUE (),
            Percent_ < .9, 3,
            Percent_ >= .9
                && Percent_ < 1, 2,
            Percent_ >= 1, 1
        )
    )

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

[shanerolle]

Hi @v-easonf-msft ,

Thank you for the assistance! That measure very nearly works, and I believe the only reason it doesn't is because I didn't specify a detail.

When the Account Numbers are stored, they are stored as something along the lines of "000.  0" where there are two spaces between the dot and the final number. This is a way to have two accounts, such as "005.  1" and "005.  2" where they are only slightly difference and a way to tell the difference. The problem is that this must be stored as a text value, and then the part of the measure: 

LEFT ( SELECTEDVALUE ( 'Table'[Account] ) & "", 1 ) <> 5,

Does not work because it is comparing to a number instead of text, I tried to change it to "5" but it does not appear to work properly when comparing and only ever uses the else rule in the if/else statement.

Sorry for the confusion, and hopefully this is an easy fix.

Thanks for the help!

Shane

[v-easonf-msft]

Hi , @shanerolle 

Take a try to change the part of measure  as below :

LEFT ( SELECTEDVALUE ( 'Table'[Account] ) & "", 3 ) <> "005"

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