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,