Forum Discussion
Count values in one table based on values from another table
Hi!
Need help with DAX. I have 2 tables. I need to show the percentage of distinct count of ItemID from Table A with multiple countries of sale from Table B.
Table A
ItemID
3
4
2
1
Table B
| ItemID | Country |
| 3 | US |
| 2 | US |
| 2 | UK |
| 10 | AUS |
| 3 | FR |
| 1 | AUS |
| 1 | GER |
Both the tables are connected using ItemID columns with 1:* relationship. The reason I cannot use the ItemID column from Table B is that it contains values that I do not need to show in this calculation, while Table A contains the ItemIDs of all the items that we are interested in. So we would like to see how many of the ItemIDs from Table A have been sold in multiple countries.
Result
International Sales% = 40.1% (Number not calculted for the example)
I'm thinking of a formula something like: (Count Distinct (A.ItemID) having Count(B.Country)>1 )/ Count Distinct (A.ItemID)
Thanks!
=DIVIDE(COUNTROWS(FILTER(ALLSELECTED(TableA[ItemID]),CALCULATE(DISTINCTCOUNT(TableB[Country]))>1)),COUNTROWS(ALLSELECTED(TableA[ItemID]))
2 Replies
- wdx223_DanielCommunity Champion
=DIVIDE(COUNTROWS(FILTER(ALLSELECTED(TableA[ItemID]),CALCULATE(DISTINCTCOUNT(TableB[Country]))>1)),COUNTROWS(ALLSELECTED(TableA[ItemID]))
- spyder_pkFrequent Visitor
Works like a charm!! Thank you!!!