Comparing AVGs in a table

[waleeed_mahmood]

Hello all,

I have a question I cant seem to solve.

I have a following table in QS.

Parent Company	Potential	Actual	COL D :Share of Wallet (SoW)	COL E: Total Visits (TVs)	Bucket
Customer A	656,130	1,019,145	155%	31	1
Customer B	220,307	109,574	50%	31	1
Customer C	239,464	80	0%	3	4
Customer D	416,667	77,915	19%	12	2
Customer E	81,418	29,043	36%	7	4
Customer F	206,897	-3,099	-1%	16	2
Customer G	82,375	78,694	96%	3	3
Customer H	90,517	5,447	6%	11	2
Customer I	306,513	14,467	5%	14	2
Customer J	78,544	86,192	110%	12	1
Customer K	35,920	676	2%	4	4
Customer L	47,893	23,256	49%	6	3
Customer M	0	13,806	-	4	3
Customer N	60,824	17,712	29%	11	2
Customer O	95,785	2,934	3%	1	4
Customer P	0	4,243	-	0	3
Customer Q	0	875	-	0	3

Share of Wallet = Actual/Potential;

AVG_SoW (COL $E$19 in Below criteria) = 40% found by 

>>>avg(aggr(Actual/Potential ,[Parent Company],[Sales Employee]));

AVG_TVs (COL $D$19 in Below criteria) = 9.76 found by

>>>> sum(aggr(count([Total Visits])/count([Parent Company]),[Parent Company],[Sales Employee]));

What i want to do is generate the Buckets column based on the following criteria:

Note: Col E is SoW and Col D is TVs in above table

=IF(AND(E2 >$E$19,D2>$D$19),"1",
IF(AND(E2>$E$19,D2<$D$19),"2",
IF(AND(E2<$E$19,D2>$D$19),"3", "4")))

My Probelm:

Im getting the following in QS

Parent Company	Potential	Actual	COL D :Share of Wallet (SoW)	COL E: Total Visits (TVs)	Bucket
Customer A	656,130	1,019,145	155%	31	1
Customer B	220,307	109,574	50%	31	-
Customer C	239,464	80	0%	3	-
Customer D	416,667	77,915	19%	12	1
Customer E	81,418	29,043	36%	7	-
Customer F	206,897	-3,099	-1%	16	-
Customer G	82,375	78,694	96%	3	-
Customer H	90,517	5,447	6%	11	-
Customer I	306,513	14,467	5%	14	-
Customer J	78,544	86,192	110%	12	-
Customer K	35,920	676	2%	4	-
Customer L	47,893	23,256	49%	6	-
Customer M	0	13,806	-	4	-
Customer N	60,824	17,712	29%	11	-
Customer O	95,785	2,934	3%	1	-
Customer P	0	4,243	-	0	-
Customer Q	0	875	-	0	-

Your help is greatly appreciated.

Please let me know if your need any clarification.

Thank you.

[rubenmarin]

Hi, you can use a variable to store $E$19, like creating a variable vAVG_SoW with value:

=avg(aggr(Actual/Potential ,[Parent Company],[Sales Employee]))

So this variable is calculated outside the table and dimensions won't affect it's result, same for D's expression, the you can use:

IF(avg(aggr(Actual/Potential ,[Parent Company],[Sales Employee]))>$(vAVG_SoW) and sum(aggr(count([Total Visits])/count([Parent Company]),[Parent Company],[Sales Employee]))>$(vAVG_TVs ), '1',
  IF...

If buckets are fixed (not affected by selections) it will be better to have this calculated on script.

[waleeed_mahmood · Accepted solution]

Hi Ruben,

Thank you for your reply. my QS script already had them in variables and i was already using the logic you mentioned above and getting those blanks in my post.

However, i was able to make it work by putting TOTAL in the AVG formulas so they dont change with dimensions when i use them in a table.

AVG_SoW = avg(TOTAL aggr(Actual/Potential ,[Parent Company],[Sales Employee]));

AVG_TVs  = sum(aggr(count(TOTAL [Total Visits])/count(TOTAL [Parent Company]),[Parent Company],[Sales Employee]));

Thank you for your help.