Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Eliminate the overlapping data

Hello, I want to show the count of new clients and existing clients (by using cards) and filtered by month (using slicer), and here is an example of the issue.   Sample data: Date Client Clie...
  • vivran22's avatar
    vivran22
    6 years ago

    @jmah

    Try this:

     
Existing Clients =
var _existing = CALCULATETABLE(DISTINCT('Client List'[Client]), 'Client List'[Client Type (Calculated Column)] = "Existing Client")
var _new = CALCULATETABLE(DISTINCT('Client List'[Client]), 'Client List'[Client Type (Calculated Column)] = "New Client")
return COUNTROWS(EXCEPT(_existing, _new))
 
New Clients =
CALCULATE(
  DISTINCTCOUNT('Client List'[Client]),
  'Client List'[Client Type (Calculated Column)] = "New Client"
)

    Bless you!
    Vivek

    Blog: vivran.in/my-blog
    Connect on LinkedIn
    Follow on Twitter

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , I am assuming the date format is dd/mm/yyyy

 

Try measures like

distinctcount(table[Client])
calculate(distinctcount(table[Client]),[Client Type] ="New Client")
calculate(distinctcount(table[Client]),[Client Type] ="Existing Client")

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak , thanks for your reply but what I want to show is 

 

Number of new clients in February: 1 (Client C)

Number of existing clients in February: 1 (only Client A, no matter how many times client C appears in February is still considered as a new client) 

 

Much appreciated. 

 

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    Hello Anonymous 

     

    You may try the following measures:

     

    New Client = 
CALCULATE (
    DISTINCTCOUNT ( 'Client List'[Client] ),
    'Client List'[Client Type (Calculated Column)] = "New Client"
)


Existing Client = 
VAR _Summerize =
    SUMMARIZE (
        'Client List',
        'Client List'[Client],
        'Client List'[Client Type (Calculated Column)]
    )
VAR _Existing =
    SUMX (
        FILTER (
            _Summerize,
            'Client List'[Client Type (Calculated Column)] = "Existing Client"
        ),
        1
    )
VAR _New =
    SUMX (
        FILTER (
            _Summerize,
            'Client List'[Client Type (Calculated Column)] = "New Client"
        ),
        1
    )
VAR _Check =
   IF ( _New <  _Existing , _Existing - _New, _New- _Existing )
RETURN
    _Check

     

     

    Output:

     

     

    Cheers!
    Vivek

    Blog: vivran.in/my-blog
    Connect on LinkedIn
    Follow on Twitter

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi vivran22, thank you so much, this really solved the problem, but when the new client only appears once in a month, the _existing will still minus _new when it not supposed to minus it. 

       

      For example: 

      DateClientClient Type (Calculated Column)
      1/1/2020ANew Client
      2/1/2020BNew Client
      1/2/2020AExisting Client
      4/2/2020CNew Client
      5/2/2020CExisting Client
      1/3/2020DNew Client
      2/3/2020AExisting Client
      3/3/2020CExisting Client

       

      With the measure, you provided it will show

      Number of new clients in March = 1

      Number of existing clients in March = 1 (it supposed to be 2 but with the _check equation, it will minus the number of new clients)

       

      Is there any way that I could compare the [client with the condition of client type = new] and [client with the condition of client type = existing]?

       

      Thanks,