Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Distinct count active customer by month

Hi,

 

I have a 'transaction table' which have transaction date, amount transacted and account code, which will linked (relationship) to a 'account table' where I can see the client ID and account type for each account.

 

I would like to count the active client ID in each month by account type.

 

I attempted this measuare "Active Client = Calculate(distinctcount('Account'[Client ID]), 'Transaction'[Amount]>0)", then I use a Pivot to tabulate the month in column and account type in row while the measure as value but it is not working.

 

Much appreciate your guidance.

 

Best regards,

 

 

Need Help
  • Floriankx's avatar
    Floriankx
    8 years ago

    So, there is a little trick.

     

    I created 2 measures:

    For Unique Account Code: 

    Active Customer:=CALCULATE([Measure];FILTER(Transactions;Transactions[Amount]>0))

     

    For Unique Client ID:

    Active Customer ID:=CALCULATE(COUNTROWS(VALUES(Customer[Client ID]));FILTER(Transactions;Transactions[Amount]>0))

     

     

    I hope this is what you are looking for.

     

    Best regards.

16 Replies

  • Floriankx's avatar
    Floriankx
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hello, 

     

    Why don't you just use COUNTROWS which wraps a VALUES.

    Measure:=COUNTROWS(VALUES(Table[Client ID]))

     

    Value ensures to count every customer only once no matter how many transactions we causes.

     

    If you add you PivotTabe and add Account Type to row content it should do as supposed.

    I have Customer Types A and B and Customer IDs from 1 to 4.

    You don't have to display Customer ID.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Is it possible to show the total of active customers for each month by account type as value instead?

      • Floriankx's avatar
        Floriankx
        Icon for Solution Sage rankSolution Sage
        8 years ago

        How does an inactive customer appear in your data?

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous

     

    When you say it's not working.  Do you get an error, or incorrect values?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi there, incorrect value.

      • Floriankx's avatar
        Floriankx
        Icon for Solution Sage rankSolution Sage
        8 years ago

        Hello,

         

        in first instance there should be a Filter in your calculate:

         "Active Client = Calculate(distinctcount('Account'[Client ID]), Filter(Transaction,'Transaction'[Amount]>0))"

         

        I don't know if the formula works then as required, but this might cause the error.

         

        Best regards.

  • KeithChu's avatar
    KeithChu
    Icon for Helper I rankHelper I
    8 years ago

    You need to use RELATED, RELATEDTABLE to get the account-related transaction. In SQL that would be Select  From 'Account' a,'Transaction' t where a.[account code] = t.[account code]. 

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi KeithChu, I am using PowerPivot here and had created a relationship between the account code.