Forum Discussion
Post ProdigySUMX filter by two columns
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
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])
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
2 Replies
- amitchandak
Super User
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
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