Help with conditional columns referencing total amounts

[lisago1978]

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

[v-juanli-msft]

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
Maggie

[lisago1978]

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

[HotChilli]

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"

[lisago1978]

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

[HotChilli]

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.

[lisago1978]

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.

[lisago1978]

Thank you!