Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct count overlap

I'm not sure how to being to solve this problem:

 

I have business data with client IDs and Location names, I've created a table with the location names as a column and a distinct count of clients as another. I realized that this distinct count is for distict client ids across all of our locations, so the disticnt counts for each row (locations) do not sum up to the total. I created a measure that sumerizes the distinct count of client Ids within each loaction so that the column totals add up using SUMMERIZE. 

 

What I want to know is, how do I figure out which locations have disinct client overlaps and by how much?

example:

 

Location    DefaultDisitinctCount    MyDistinctCountMeasure

AAA            100                                   100

BBB               50                                     50

Total           145                                   150

 

So this tells me that there are 5 distinct clients that overlap for these 2 locations

 

I know the tricky part is going to be that multiple locations can have client overlap.

 

Any help would be great!

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    This will give you a table with common values. Change as per need

     

     

    Table = INTERSECT(ADDCOLUMNS(FILTER(Sales,Sales[Sales Date] >= date(2018,06,30)),"AA",Sales[Sales Date]),ADDCOLUMNS(FILTER(Sales,Sales[Sales Date] >= date(2018,06,29)),"AA",Sales[Sales Date]))

     

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

       

      I'm not sure how I would use intersect in this case. I don't have 2 tables of data and I want to see the intersect between all of the locations, which are values in a single column.

       

       

      Thanks

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        This will give you a table with common values. Change as per need

         

         

        Table = INTERSECT(ADDCOLUMNS(FILTER(Sales,Sales[Sales Date] >= date(2018,06,30)),"AA",Sales[Sales Date]),ADDCOLUMNS(FILTER(Sales,Sales[Sales Date] >= date(2018,06,29)),"AA",Sales[Sales Date]))

         

         

        Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
        Thanks.