Measure for frequency of transactions by Customer ID

[Alenth]

Hi everyone,

Apologies, I'm quite new to Powerbi & Dax.

Need help with a simple problem - I need to create a measure (or calculated column if that ends up being a better choice?) that sums how frequently unique customers have transacted with us - the end goal being that I can have a visual table that lets me have frequency of transaction in one column, and distinct count of unique customer ids on the right. Ideally I would also like to group frequency of transactions so we can identify how many customers transact only once, or 5+ times or 10+ times in a year (but unsure if I can do this with a measure as well).

End result visual example:

Frequency of Transaction	Customer Id (distinct count)
1	500
2	600
3	450
4	300
5 etc	120

Data is extremely simple - each customer transaction is one row of detailed data, that has Sales[Customer ID] and  Sales[Transaction count] (which always appears as "1").

Example data:

Customer ID	Brand Purchased	Date	Transaction Count
Joe-12345	Brand 1	01/01/2018	1
Bob-6234	Brand 1	06/07/2018	1
Joe-12345	Brand 2	12/01/2018	1
Anne-7894	Brand 2	20/07/2018	1
Anne-7894	Brand 2	20/02/2018	1

Thanks so much for any assistance!

[v-juanli-msft · Accepted solution]

Hi @Alenth 

Create a column

Frequency of Transaction = CALCULATE(COUNT(Table1[Transaction Count]),ALLEXCEPT(Table1,Table1[Customer ID]))

Create a measure

Customer Id (distinct count) = CALCULATE(DISTINCTCOUNT(Table1[Customer ID]),FILTER(ALL(Table1),Table1[Frequency of Transaction]=MAX(Table1[Frequency of Transaction])))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wilfuentes]

Hi @v-juanli-msft Hello, I'm wondering if you could help me by explaining how I could modify your solution in order to take the frequency of transactions by Customer Id and Brand Purchace? I really apreciate your help.

[Anonymous]

What if we had other columns like a "size" that we want to filter by. As in, we want the frequency of transaction to calculate for ONLY the transactions (for each customer) that meet the filtered criteria. Is this possible?

[Alenth]

Thanks so much @v-juanli-msft , worked like a charm.

Really appreciate your help!