Formatting Data

[Anonymous]

Hello,

I’ve gotten stuck at a few places trying to conditionally format my data, and hopefully this makes sense. I'm tring to build a script in order to make each cell be given a 0,1,or a 2 in a seprate column. And I am using these values (0-2) to refer back to a color table to be able to conditioanlly format by a each row. The problems I have run into is that there are percentages and integers in each column of my data, with the percentages, some are considered good when they’re higher and others are the opposite so the conditions are different for each of the different metrics. I want to be able to slice my data based on BU and by metric. 

So then I decided on building a script to go through each order(which is each metric) and then based on which Business Unit it was for, try and conditionally format it like that. So make it either 0,1,2 for the color scheme. Hopefully the picture with my code will make sense, but I’m not getting any output when I run the code…

PLS Help

AllCompany(JAN) =

                    IF('Sheet1'[Order]==1,

                        IF([Division]=="-All of company",

                            IF('Sheet1'[Jan]>=95,2,

                            IF('Sheet1'[Jan]<95 && 'Sheet1'[Jan]>71,1,

                            IF('Sheet1'[Jan]<=71,0,

                        IF([Division]=="Bank",

                            IF('Sheet1'[Jan]>=23,2,

                            IF('Sheet1'[Jan]<23 && 'Sheet1'[Jan]>17,1,

                            IF('Sheet1'[Jan]<=17,0,

[Anonymous]

Also, this is just part of my code, i know i need a third argument for the IF funtion. I tried it with a sample third argument and did not get any output in the colomn. 

---

@Anonymous wrote:

Hello,

I’ve gotten stuck at a few places trying to conditionally format my data, and hopefully this makes sense. I'm tring to build a script in order to make each cell be given a 0,1,or a 2 in a seprate column. And I am using these values (0-2) to refer back to a color table to be able to conditioanlly format by a each row. The problems I have run into is that there are percentages and integers in each column of my data, with the percentages, some are considered good when they’re higher and others are the opposite so the conditions are different for each of the different metrics. I want to be able to slice my data based on BU and by metric. 

So then I decided on building a script to go through each order(which is each metric) and then based on which Business Unit it was for, try and conditionally format it like that. So make it either 0,1,2 for the color scheme. Hopefully the picture with my code will make sense, but I’m not getting any output when I run the code…

PLS Help

AllCompany(JAN) =

                    IF('Sheet1'[Order]==1,

                        IF([Division]=="-All of company",

                            IF('Sheet1'[Jan]>=95,2,

                            IF('Sheet1'[Jan]<95 && 'Sheet1'[Jan]>71,1,

                            IF('Sheet1'[Jan]<=71,0,

                        IF([Division]=="Bank",

                            IF('Sheet1'[Jan]>=23,2,

                            IF('Sheet1'[Jan]<23 && 'Sheet1'[Jan]>17,1,

                            IF('Sheet1'[Jan]<=17,0,

---

[dax]

Hi y2jz,

I think the if condition should be measure=if(<condition>=xxx, true condition, false condition). I saw you use "==" in measure or column, will this prompt error information?

In addition, if possible, could you please inform me more detailed information(such as your data sample and your expected output)? Then I will help you more correctly.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I guess I would need an ELIF function like in python that would read as if a condition is not met fot the first IF function than it will move onto the next. @dax 

[Anonymous]

This still did not work for me :

AllCompany(JAN) =

                  IF('Sheet1'[Order]=1,

                        IF([Division]="-All of Company",

                               IF('Sheet1'[Jan]>=95,2,

                               IF('Sheet1'[Jan]<95 && 'Sheet1'[Jan]>71,1,

                               IF('Sheet1'[Jan]<=71,0,

                         IF([Division]="Bank",

                                IF('Sheet1'[Jan]>=23,2,

                                IF('Sheet1'[Jan]<23 && 'Sheet1'[Jan]>17,1,

                                IF('Sheet1'[Jan]<=17,0,

To give you more detail, in one column I am trying to assign each cell a 0, 1, or a 2 based on specified conditions. In the code, each order number is a different metric and thererfore will have different conditions, but there are also different divisions, such as "all of comapny" and "bank" listed above, that have different conditions. So I am trying to write a code that will go through my data and if it is order 1 it will go through the order 1 IF function, than if it is the bank Division then it will go through the bank IF function and return a value of 0,1, or 2 in the new column. Here is a screen shot of some of the data to get a better understanding. 

Thank you