Column totals sumx and Hasonefilter in DAX

[Beehive]

Hi All,

I have researched the solution on different threads to calculate the correct answer for summing columns that include a row of calculated measures but continue to get an error with the Filter in Sumx as follows.  Can someone please assist?

Price variance Amount CY LY = if(HASONEFILTER

(viewAllSalesTransactions[ItemName]), if(calculate([Total Sales LY])>0,(CALCULATE([Average Sales Price CY])-CALCULATE([Average Sales Price LY]))*CALCULATE([Total Quantity CY]),0),

sumx( FILTER('viewAllSalesTransactions','viewAllSalesTransactions'[Total Sales LY] > 0,('viewAllSalesTransactions'[Average Sales Price CY]-'viewAllSalesTransactions'[Average Sales Price LY])*'viewAllSalesTransactions'[Total Quantity CY])))

Error message:

Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2.

[Ashish_Mathur]

Hi,

Share a dataset and also show the expected result.

[Beehive]

I hope the following makes sense.  The expected outcome is in red text, however, without the Sumx the measure will calculate 35 for the column total ie. (13.33 - 11) * 15 = 35

1	C	D	E	F	G	H	I	J	K	L
2		Total Sale CY	Total Sales LY	Sales Amount Variance CY LY	Average Sales Price CY	Average Sales Price LY	Price Variance Amount CY LY	Price Variance Amount CY LY (Formula)	Total Quantity CY	Total Quantity LY
3	Product A	100	110	-10	10	11	-10	=IF(H7>0,+(G7-H7)*K7,0)	10	10
4	Product B	100		100	20	0	0	=IF(H8>0,+(g8-h8)*K8,0)	5	0
5	Total	200	110	90	13.33	11.00	-10		15	10

[Ashish_Mathur · Accepted solution]

Hi,

Try this

=SUMX(FILTER(SUMMARIZE(VALUES(Data[Product]),[Product],"ABCD",[Average Sales Price CY],"EFGH",[Average Sales Price LY]),[EFGH]>0),([ABCD]-[EFGH])*[Total Quantity CY])

Hope this helps.