Formula not calculating at the aggregated/matrix level

[Prakest]

I'm having an issue where the if statement does not seem to recognize a comparison across multiple columns at an aggregated level. If all GA, Salesflash, WF, and ARR columns are the same it should return "Equivalent", otherwise it should return "Different".

Here's a snippet of the data:

This is how I accomplished it when the data was in a different format (pre aggregregated), but it no longer works after additional fields were added. I tried adding helper columns as seen in the first screenshot but that made no difference.

AmountCheck1 = 
IF([GA_Amt]=[Totaldiv4]
|| [WF_Amt]=[Totaldiv4]
|| [SalesFlash_Amt]=[Totaldiv4]
|| [ARR_Amt]=[Totaldiv4], "Equivalent",
"Difference")

[Anonymous]

Hi @Prakest ,

Could you share just a sample data with maybe 10 rows of data ? I can't identify whether the issue is coming from the calculated column or the matrix

BR

[Prakest]

@Anonymous 

edit: To clarify, this is raw data

Here's a data sample:

Product Id	Order	Default Sale Date	Rev Prod Category based OT	Sales Order Line One Time	Prior Yr Adj	New vs Ren	SalesFlash_Amt	ARR_Amt	GA_Amt	WF_Amt
1	O-092	6/21/2022	Y		N	Renewal	40,047	40,047	40,047
1	O-092	6/21/2022		N	N	Renewal		40,047	40,047
1	O-092	6/21/2022		N	N	Renewal				40,047
1	O-092	6/21/2022		N	N	Renewal				(40,047)
1	O-092	6/21/2022		N	N	Renewal				40,047
2	O-120	6/22/2022		N	N	New	15,400	15,400	15,400
3	O-120	6/22/2022	Y		N	New	12,000
3	O-120	6/22/2022		N	N	New		12,000	12,000
4	O-344	6/29/2022		N	N	New	3,000	3,000	3,000
4	O-344	6/29/2022		N	N	New				3,000
5	O-344	6/29/2022		N	N	New	3,325	3,325	3,325
5	O-344	6/29/2022		N	N	New				3,325
6	O-344	6/29/2022		N	N	New	122,155	122,155	122,155
6	O-344	6/29/2022		N	N	New				122,155
7	O-344	6/29/2022	Y		N	New	150,000
7	O-344	6/29/2022		N	N	New		150,000	150,000
7	O-344	6/29/2022		N	N	New				150,000
8	O-344	6/29/2022		N	N	New	91,500	91,500	91,500
8	O-344	6/29/2022		N	N	New				91,500
9	O-344	6/29/2022		N	N	New	45,750	45,750	45,750
9	O-344	6/29/2022		N	N	New				45,750
10	O-344	6/29/2022		N	N	New	45,750	45,750	45,750
10	O-344	6/29/2022		N	N	New				45,750

[Anonymous]

@Prakest ,

may i know how you derived totaldiv4?

[Prakest]

@Anonymous This is where totaldiv4 is coming from:

Totaldiv4 = ([GA_Amt]+[WF_Amt]+[ARR_Amt]+[SalesFlash_Amt])/4

There may be (and probably is) a better method to flag if all 4 "..._Amt" columns are the same. 

[Anonymous]

Hi @Prakest 

Try creating a calculated column with 

Totaldiv4 = 

Var SalesFlash=
IF(Sheet1[SalesFlash_Amt]>=1,1,0)

Var ARRamt= 
IF(Sheet1[ARR_Amt]>=1,1,0)

Var GAamt= 
IF(Sheet1[GA_Amt]>=1,1,0)

Var WFAmt= 
IF(Sheet1[WF_Amt]>=1,1,0)


RETURN

IF(SalesFlash+ARRamt+GAamt+WFAmt>=4,"Equivalent","Different")

BR

[Prakest]

Hi @Anonymous 

Unfortunately this didn't work. I need to create a flag if any of the columns are different from each other, not just to do a value floor check.

I tried to modify your formula like this but it exhibited the same behavior as before.

TotalDiv4 = 

Var Tdiv=
([ARR_Amt]+[GA_Amt]+[WF_Amt]+[SalesFlash_Amt])/4

Var SF=
IF(Tdiv < [SalesFlash_Amt],1,0)

Var ARR= 
IF(Tdiv = [ARR_Amt],1,0)

Var GA= 
IF(Tdiv = [GA_Amt],1,0)

Var WF= 
IF(Tdiv = [WF_Amt],1,0)


RETURN

SF+WF+ARR+GA