Forum Discussion
Help with conditional columns referencing total amounts
I need some insight into how to create the following in either power query or power BI
I have a series of zipcodes that I am referencing the individual zip code % yes confidence interval (margin or effor) to the total % yes confidence interval. I am trying to replicate the 'cond' column in either powery query or power BI.
I need to do this many times so rather than the cell location- the name 'Total'[LCI] would be better to reference.
Here is my excel code
=IF(AND(F6<$F$17,G6<$G$17),"LOWER",IF(AND(F6>$F$17,G6>$G$17),"HIGHER","SAME"))
Here is the data
| Zipcode | Count | Denominator | % yes | LCI | UCI | cond | tier |
| 90001 | 28 | 1389 | 2.0 | 1.3 | 2.9 | LOWER | 2.0 |
| 90002 | 8 | 167 | 4.8 | 2.1 | 9.4 | SAME | 3.0 |
| 90003 | 36 | 821 | 4.4 | 3.1 | 6.1 | SAME | 3.0 |
| 90004 | 29 | 645 | 4.5 | 3.0 | 6.5 | SAME | 3.0 |
| 90005 | 7 | 146 | 4.8 | 1.9 | 9.9 | SAME | 3.0 |
| 90006 | 23 | 620 | 3.7 | 2.4 | 5.6 | SAME | 2.0 |
| 90007 | 5 | 187 | 2.7 | 0.9 | 6.2 | SAME | 2.0 |
| 90008 | 34 | 559 | 6.1 | 4.2 | 8.5 | HIGHER | 4.0 |
| 90009 | 10 | 234 | 4.3 | 2.0 | 7.9 | SAME | 3.0 |
| 90010 | 28 | 789 | 3.5 | 2.4 | 5.1 | SAME | 2.0 |
| 90011 | 46 | 1261 | 3.6 | 2.7 | 4.9 | SAME | 2.0 |
| Total | 858 | 21846 | 3.9 | 3.7 | 4.2 | SAME | 4.0 |
7 Replies
Hi lisago1978
I'm not very clear about your criteria.
As tested in excel, "UCI" is "F" column, "cond" is "G" column.
Could you show me your expected result?
Or clear me more?
Best Regards
MaggieThanks 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"
