Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, community,
I have a sales table, in which each row indicates a transaction.
My end goal is to be able to show a consolidation of the number of customers in each fixed transaction range, exemplified in the "Number of transactions" column below.
Number of transactions | Number of customers |
1 | |
2 | |
3 | |
4 | |
5 | |
6 | |
7 | |
8 | |
9 | |
>=10 |
--
Let's look at the sample data:
1) This is a sales table, with 81 transactions and 8 different customers in a determined period
Customer Name | Transaction Date | Transaction # |
A | 20/01/2023 | X1262 |
A | 28/02/2023 | X1718 |
B | 03/02/2023 | X1426 |
C | 09/02/2023 | X1496 |
D | 09/02/2023 | X1508 |
E | 25/01/2023 | X1311 |
E | 27/01/2023 | X1334 |
E | 27/01/2023 | X1336 |
E | 02/02/2023 | X1414 |
E | 07/02/2023 | X1478 |
F | 15/02/2023 | X1574 |
F | 17/02/2023 | X1613 |
F | 02/03/2023 | X1762 |
G | 10/01/2023 | X1181 |
G | 23/01/2023 | X1282 |
G | 23/01/2023 | X1283 |
G | 31/01/2023 | X1362 |
G | 31/01/2023 | X1363 |
G | 13/02/2023 | X1536 |
G | 13/02/2023 | X1537 |
G | 22/02/2023 | X1645 |
G | 01/03/2023 | X1745 |
G | 01/03/2023 | X1746 |
G | 17/01/2023 | X1227 |
H | 07/02/2023 | X1459 |
H | 27/02/2023 | X1543 |
2) If I was to do this in excel, I'd create an intermediate table with the number of transactions per customer
A | 2 |
B | 1 |
C | 1 |
D | 1 |
E | 5 |
F | 3 |
G | 11 |
H | 2 |
3) And then I'd use another CountIF to aggregate the number of customers per number of transactions
Number of transactions | Number of customers |
1 | 3 |
2 | 2 |
3 | 1 |
4 | 0 |
5 | 1 |
6 | 0 |
7 | 0 |
8 | 0 |
9 | 0 |
>=10 | 1 |
Seems very basic, but I didn't manage to do this 3rd step in PowerBI and it's been a few hours now 😞
Solved! Go to Solution.
Number Of Customer=COUNTROWS(FILTER(SUMMARIZE(Sales,Sales[Customer Name],"@cnt",COUNTROWS(Sales)),[@cnt]=MAX(DimTable[Number of Transactions]))
Number Of Customer=COUNTROWS(FILTER(SUMMARIZE(Sales,Sales[Customer Name],"@cnt",COUNTROWS(Sales)),[@cnt]=MAX(DimTable[Number of Transactions]))
Thanks! That worked (:
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |