Distinct count active customer by month

[Anonymous]

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,

[KeithChu]

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]

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

[Floriankx]

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]

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

[Floriankx]

How does an inactive customer appear in your data?

[Anonymous]

Right now, it show the same number for each month.

[Floriankx]

If Customers appear with an amount of 0 you can do the following:

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

You could also wrap the two measures into each other but you should first check if this is working.

[Anonymous]

@Floriankx, it is showing the same number for each month.

[Floriankx]

Maybe you can provide some sample data. Here is mine:

Transactions:

Amount         Customer ID Date

0	1	02. Jan
400	2	01. Mrz
153	3	05. Apr
855	1	10. Mrz
445	4	04. Feb
843	3	08. Mrz
648	2	28. Feb
789	4	15. Jan
145	1	09. Apr
843	2	22. Mrz
879	4	18. Jan
349	3	23. Feb
154	3	30. Jan

Class

Custimer ID        Class

1	A
2	A
3	B
4	B

I related the table via Customer ID

Those are my Measures in the Transaction table:

Measure:=COUNTROWS(VALUES(Transactions[Customer ID]))

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

Putting these Measures into a PivotTable Date as Column for Month Values it results in:

In January for Customer 1 you see the difference. The Amount is 0 so Active Customer is blank but for Measure which counts customer no matter which amount there is 1.

Best regards.

[Anonymous]

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

The below is my sample table:

Trx Date	Account Code	Amount
1-Mar-18	A7	119
2-Mar-18	A9	175
3-Mar-18	A3	120
4-Mar-18	A9	107
5-Mar-18	A2	105
5-Apr-18	A4	146
6-Apr-18	A9	169
7-Apr-18	A4	109
8-Apr-18	A6	173
9-Apr-18	A6	179
20-May-18	A2	153
20-May-18	A3	195
20-May-18	A2	131
21-May-18	A5	175
22-May-18	A6	146
23-May-18	A3	185
23-May-18	A2	191
23-May-18	A4	179

Account Code	Account Type	Client ID
A1	Basic	B1
A2	Premium	B1
A3	Basic	B2
A4	Basic	B2
A5	Basic	B3
A6	Basic	B4
A7	Basic	B5
A8	Basic	B5
A9	Premium	B5
A10	Premium	B6

I had created the relationship using account code.

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

Sum of Amount	Column Labels
	Mar	Apr	May	Grand Total
Basic	239	607	880	1726
B2	120	255	559	934
B3			175	175
B4		352	146	498
B5	119			119
Premium	387	169	475	1031
B1	105		475	580
B5	282	169		451
Grand Total	626	776	1355	2757

I would like to achieve the below instead:

Active Client
	Mar	Apr	May	Grand Total
Row Labels
Basic	2	2	3	4
Premium	2	1	1	2

[Floriankx · Accepted solution]

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]

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

[Floriankx]

Your Table says 'Sum of Amount'.

Seems like On Board Sum of PivotTable. I think there is also the possibility of 'Count' instead of 'Sum'.

I prefer creating Measure so then you could use my measures as posted before.

Best regards.

[Phil_Seamark]

Hi @Anonymous

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

[Anonymous]

Hi there, incorrect value.

[Floriankx]

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.