Formula not calculating at the aggregated/matrix level

Question

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:&nbsp;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.&nbsp;AmountCheck1 = 
IF([GA_Amt]=[Totaldiv4]
|| [WF_Amt]=[Totaldiv4]
|| [SalesFlash_Amt]=[Totaldiv4]
|| [ARR_Amt]=[Totaldiv4], "Equivalent",
"Difference")&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;Prakest&nbsp;,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 matrixBR

prakest · Answer

Anonymous&nbsp;edit: To clarify, this is raw dataHere's a data sample:Product IdOrderDefault Sale DateRev Prod Category based OTSales Order Line One TimePrior Yr AdjNew vs RenSalesFlash_AmtARR_AmtGA_AmtWF_Amt1O-0926/21/2022Y&nbsp;NRenewal&nbsp;&nbsp;&nbsp;&nbsp; 40,047&nbsp;&nbsp;&nbsp;&nbsp; 40,047&nbsp;&nbsp;&nbsp;&nbsp; 40,047&nbsp;1O-0926/21/2022&nbsp;NNRenewal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 40,047&nbsp;&nbsp;&nbsp;&nbsp; 40,047&nbsp;1O-0926/21/2022&nbsp;NNRenewal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 40,0471O-0926/21/2022&nbsp;NNRenewal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (40,047)1O-0926/21/2022&nbsp;NNRenewal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 40,0472O-1206/22/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp; 15,400&nbsp;&nbsp;&nbsp;&nbsp; 15,400&nbsp;&nbsp;&nbsp;&nbsp; 15,400&nbsp;3O-1206/22/2022Y&nbsp;NNew&nbsp;&nbsp;&nbsp;&nbsp; 12,000&nbsp;&nbsp;&nbsp;3O-1206/22/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,000&nbsp;&nbsp;&nbsp;&nbsp; 12,000&nbsp;4O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,000&nbsp;4O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,0005O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,325&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,325&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,325&nbsp;5O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,3256O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp; 122,155&nbsp;&nbsp; 122,155&nbsp;&nbsp; 122,155&nbsp;6O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 122,1557O-3446/29/2022Y&nbsp;NNew&nbsp;&nbsp; 150,000&nbsp;&nbsp;&nbsp;7O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp; 150,000&nbsp;&nbsp; 150,000&nbsp;7O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 150,0008O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp; 91,500&nbsp;&nbsp;&nbsp;&nbsp; 91,500&nbsp;&nbsp;&nbsp;&nbsp; 91,500&nbsp;8O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 91,5009O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp; 45,750&nbsp;&nbsp;&nbsp;&nbsp; 45,750&nbsp;&nbsp;&nbsp;&nbsp; 45,750&nbsp;9O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 45,75010O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp; 45,750&nbsp;&nbsp;&nbsp;&nbsp; 45,750&nbsp;&nbsp;&nbsp;&nbsp; 45,750&nbsp;10O-3446/29/2022&nbsp;NNNew&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 45,750

anonymous · Answer

Prakest&nbsp;,may i know how you derived totaldiv4?

prakest · Answer

Anonymous&nbsp;This is where totaldiv4 is coming from:Totaldiv4 = ([GA_Amt]+[WF_Amt]+[ARR_Amt]+[SalesFlash_Amt])/4There may be (and probably is) a better method to flag if all 4 "..._Amt" columns are the same.&nbsp;

anonymous · Answer

Hi&nbsp;Prakest&nbsp;Try creating a calculated column with&nbsp;Totaldiv4 =

Var SalesFlash=
IF(Sheet1[SalesFlash_Amt]&gt;=1,1,0)

Var ARRamt= 
IF(Sheet1[ARR_Amt]&gt;=1,1,0)

Var GAamt= 
IF(Sheet1[GA_Amt]&gt;=1,1,0)

Var WFAmt= 
IF(Sheet1[WF_Amt]&gt;=1,1,0)

RETURN

IF(SalesFlash+ARRamt+GAamt+WFAmt&gt;=4,"Equivalent","Different")BR

Forum Discussion

Formula not calculating at the aggregated/matrix level

6 Replies

Related content

Non-aggregated matrix?

Stop Data Aggregation on a Matrix

Tree Calculation/Aggregated Values Matrix

If formula

formula

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