Assign COUNTIF to row range (number of customers per number of transactions intervals)

[Jess123]

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 😞

[wdx223_Daniel · Accepted solution]

Number Of Customer=COUNTROWS(FILTER(SUMMARIZE(Sales,Sales[Customer Name],"@cnt",COUNTROWS(Sales)),[@cnt]=MAX(DimTable[Number of Transactions]))

[Jess123]

Thanks! That worked (: