cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shanerolle
Frequent Visitor

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

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

 

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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.

 

View solution in original post

3 REPLIES 3
v-easonf-msft
Community Support
Community Support

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.

 

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

 

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.
 

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors