SUMX filter by two columns

[Saxon10]

I have two tables are data and report.

In data table the following columns are country, value and count and report table country code only.

I am trying to get the sum of value according to the country code with count column equal to 1 only from data table into report table.

I am applying following DAX in report table SUM of value = SUMX(FILTER(DATA,DATA[Country]=EARLIER(REPORT[Country])),DATA[Value]) it’s giving overall sum of value but I need sum of value based on the count column equal to 1.

I am trying to add one more filter in existing DAX code but I am receiving error. (Fillter(Data,data{count}=1)

DATA		REPORT
Country	Value	COUNT		Country	Desired Result (Sum of value)		COUNT	1
Ind	0.038	1		Aus	0.017
Ind	0.038	2		Ind	0.076		Row Labels	Sum of Value
Ind	0.038	3		Ue	0.068		Aus	0.017
Ind	0.038	4		Uk	1.591		Ind	0.076
Uk	0.033	1		Us	0.073		Ue	0.068
Uk	0.033	2					Uk	1.591
Uk	0.033	3					Us	0.073
Aus	0.017	1					Grand Total	1.825
Aus	0.017	2
Us	0.073	1
Ue	0.068	1
Ind	0.038	1
Ind		1
Aus		1
Uk	1.558	1

Any adivse please

[amitchandak · Accepted solution]

@Saxon10 , You are create a column, In that case you can try

SUMX(FILTER(DATA,DATA[Country]=EARLIER(REPORT[Country]) && [count] =1 ),DATA[Value])

I think this measure should do too, if count is a column

SUMX(FILTER(DATA,DATA[count] =1 ),DATA[Value])

[Jihwan_Kim · Accepted solution]

Hi, @Saxon10 

Please check the below picture and the sample pbix file's link down below.

I created for both calculated column and measure.

https://www.dropbox.com/s/3cg0igaph66sik0/saxonv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak · Accepted solution]

@Saxon10 , You are create a column, In that case you can try

SUMX(FILTER(DATA,DATA[Country]=EARLIER(REPORT[Country]) && [count] =1 ),DATA[Value])

I think this measure should do too, if count is a column

SUMX(FILTER(DATA,DATA[count] =1 ),DATA[Value])