Forum Discussion

spyder_pk's avatar
spyder_pk
Frequent Visitor
2 years ago
Solved

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

ItemIDCountry
3US
2US
2UK
10AUS
3FR
1AUS
1GER

 

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_Daniel's avatar
    wdx223_Daniel
    Community Champion

    =DIVIDE(COUNTROWS(FILTER(ALLSELECTED(TableA[ItemID]),CALCULATE(DISTINCTCOUNT(TableB[Country]))>1)),COUNTROWS(ALLSELECTED(TableA[ItemID]))