cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Floriankx Established Member
Established Member

Re: Distinct count active customer by month

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.

TeeTreeThree Regular Visitor
Regular Visitor

Re: Distinct count active customer by month

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

Floriankx Established Member
Established Member

Re: Distinct count active customer by month

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

Transactions:

Amount         Customer ID Date

0102. Jan
400201. Mrz
153305. Apr
855110. Mrz
445404. Feb
843308. Mrz
648228. Feb
789415. Jan
145109. Apr
843222. Mrz
879418. Jan
349323. Feb
154330. Jan

Class

Custimer ID        Class

1A
2A
3B
4B

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:

Snip Countrow_Values 2.PNG

 

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.

TeeTreeThree Regular Visitor
Regular Visitor

Re: Distinct count active customer by month

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 Established Member
Established Member

Re: Distinct count active customer by month

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.

Highlighted
Floriankx Established Member
Established Member

Re: Distinct count active customer by month

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))

 

Snip Countrow_Values 3.PNG

 

I hope this is what you are looking for.

 

Best regards.

View solution in original post

TeeTreeThree Regular Visitor
Regular Visitor

Re: Distinct count active customer by month

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 464 members 3,955 guests
Please welcome our newest community members: