Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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.
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].
Hi @KeithChu, I am using PowerPivot here and had created a relationship between the account code.
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.
Is it possible to show the total of active customers for each month by account type as value instead?
How does an inactive customer appear in your data?
Right now, it show the same number for each 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.
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.
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 |
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.
@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.
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.
Hi @Anonymous
When you say it's not working. Do you get an error, or incorrect values?
Hi there, incorrect value.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |