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 t...
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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

My apology and I really appreciate your patience and time for helping me on this.

 

The below is my sample table:

 

Trx DateAccount CodeAmount
1-Mar-18A7119
2-Mar-18A9175
3-Mar-18A3120
4-Mar-18A9107
5-Mar-18A2105
5-Apr-18A4146
6-Apr-18A9169
7-Apr-18A4109
8-Apr-18A6173
9-Apr-18A6179
20-May-18A2153
20-May-18A3195
20-May-18A2131
21-May-18A5175
22-May-18A6146
23-May-18A3185
23-May-18A2191
23-May-18A4179

 

Account CodeAccount TypeClient ID
A1BasicB1
A2PremiumB1
A3BasicB2
A4BasicB2
A5BasicB3
A6BasicB4
A7BasicB5
A8BasicB5
A9PremiumB5
A10PremiumB6

 

 

I had created the relationship using account code.

 

I can get the amount of transaction for each unique Client Code below:

 

Sum of AmountColumn Labels   
 MarAprMayGrand Total
Basic2396078801726
B2120255559934
B3  175175
B4 352146498
B5119  119
Premium3871694751031
B1105 475580
B5282169 451
Grand Total62677613552757

 

 

I would like to achieve the below instead:

Active Client   
 MarAprMayGrand Total
Row Labels   
Basic2234
Premium2112

 

 

Floriankx's avatar
Floriankx
Solution Sage
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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Floriankx, thank you very much. Now the FILTER function better and also learn new function like Countrows. I will look into it further when have time.

     

    Reallly appreciate your time and patience for teaching me.