Forum Discussion
Help with conditional columns referencing total amounts
Thanks for responding and I am sorry I realized the forgot to include the row and column specifications.
Here it is
| 1 | B | C | D | E | F | G | H | I |
| 2 | Zipcode | Count | Denominator | % yes | LCI | UCI | cond | tier |
| 3 | 90001 | 28 | 1389 | 2.0 | 1.3 | 2.9 | LOWER | 2.0 |
| 4 | 90002 | 8 | 167 | 4.8 | 2.1 | 9.4 | SAME | 3.0 |
| 5 | 90003 | 36 | 821 | 4.4 | 3.1 | 6.1 | SAME | 3.0 |
| 6 | 90004 | 29 | 645 | 4.5 | 3.0 | 6.5 | SAME | 3.0 |
| 7 | 90005 | 7 | 146 | 4.8 | 1.9 | 9.9 | SAME | 3.0 |
| 8 | 90006 | 23 | 620 | 3.7 | 2.4 | 5.6 | SAME | 2.0 |
| 9 | 90007 | 5 | 187 | 2.7 | 0.9 | 6.2 | SAME | 2.0 |
| 10 | 90008 | 34 | 559 | 6.1 | 4.2 | 8.5 | HIGHER | 4.0 |
| 11 | 90009 | 10 | 234 | 4.3 | 2.0 | 7.9 | SAME | 3.0 |
| 12 | 90010 | 28 | 789 | 3.5 | 2.4 | 5.1 | SAME | 2.0 |
| 13 | 90011 | 46 | 1261 | 3.6 | 2.7 | 4.9 | SAME | 2.0 |
| 14 | Total | 858 | 21846 | 3.9 | 3.7 | 4.2 | SAME | 4.0 |
And the formula is =IF(AND(F3<$F$14,G3<$G$14),"LOWER",IF(AND(F3>$F$14,G3>$G$14),"HIGHER","SAME"))
So I am doing a logical formula for the lower and upper CIs. Wherein if the value is lower than both total LCI and UCI it is lower. If the value is higher than both total LCI and UCI than it is higher otherwise it is the same. Let me know what else you need from me.
Thanks
Lisa
In Power Query, Duplicate the table and 'Keep Rows' -> Bottom 1.
This will give you a 1 row table with the Totals line in it. Re-name this table Totals.
In the original table, add a column with this code
if [LCI] < List.First(Totals[LCI]) and [UCI] < List.First(Totals[UCI]) then "LOWER" else if [LCI] > List.First(Totals[LCI]) and [UCI] > List.First(Totals[UCI]) then "HIGHER" else "SAME"
Thank you!
Thank you for the response. The code didn't exactly work. It said I was missing a comma but couldn't put it in. The other problem is that the Totals table will need to have more than 1 row since I have multiple indicators and multiple totals in the same table. So I would need code that matches a specific text indicator for example "low birth weight" and a datayear "2010" and then finds the LCI and UCI in that row to compare to the data table with matching text and year indicator rows. I am happy to send the 2 tables I am refering to if this helps.
Thanks
Lisa
I knew your real data was different!
Send the tables and i'll have a look.
For the initial "missing comma" issue, there's either a syntax error at your side OR the column headings and types are wrong in the 2 tables. The code works when the tables are set up correctly.
Thanks, I don't think I can send an attachment with my tables using this community forum, it you know of a way let me know. I can't share my dashboard with you because I am limited only to those in my organization in terms of how our permissions are set up. If you have an email address I can email the tables to you.