- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Get average of number of brands purchased
Hi everyone! I've seen this question answered in different forums but for some reason solutions won't work for me.
I have a column with Customer ID and another one with Brands. Each customer can buy any of the 12 brands many times.
I need to know the average brands bought per customer.
For example:
Customer 1 | Brand A |
Customer 1 | Brand B |
Customer 1 | Brand C |
Customer 2 | Brand A |
Customer 2 | Brand A |
So here customer 1 bought 3 brands and customer 2 bought 1 brand twice. The number I'm looking for would be 1.5. This is 3 brands + 1 brand divided by 2 customers.
I've tried a lot of things but couldn't find a good DAX formula since they all return either the 12 brands or the amount of customers.
I'd appreciate your help! Thanks everyone!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this in a card. or in the table with customers as rows
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here is the formula for a new measure, put this measure into a card to get the final average number of all brands for all customers:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You're completely right, thanks for the correction! In my example the number I'd be looking for would be 2.
It sort of worked. I have 55K entries, divided in 790 unique customers and 12 brands. All customers buy at least 1 brand, however your formula gave me 0.02 as a result.
Any ideas why this is happening? Thank you again so much for your help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oh okay - in that case the formula is incorrect.
You are getting that number because its taking the number of unique brands (12) and dividing it by the number of unique customers (790).
12/790 = 0.015 rounds up to 0.02
I'll think of a solution and come back to you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks! So this is what I get:
The Brand column is literally the Column with the brands shown as distinct count (no formula here). The AVG Brands Column is the formula you kindly provided which works perfectly individually. However, I'd need the total to be the sum of each customer's AVG Brand divided by the number of customers. Right now it is dividing the 12 brands just like you said.
Any ideas on how to make it work better? I feel we are super close 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this in a card. or in the table with customers as rows
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Genius! Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Glad to help!
Did you get something like 1.2 AVG Brands? Meaning that most customers don't deviate too much away from buying only one brand?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That's right! 1.16 which kind of what I was expecting. The business translation (in case you were curious) will be bringing that number up through cross-selling. Thanks again!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi, try
DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Olgad! I had tried that, however it returns the total number of brands that I have, and not the average per customer.
Any ideas? Thank you so much for your help!

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Join our Community Sticker Challenge 2025
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Subject | Author | Posted | |
---|---|---|---|
04-16-2024 07:05 PM | |||
02-20-2024 06:37 AM | |||
05-03-2024 03:14 AM | |||
03-28-2024 07:10 PM | |||
Anonymous
| 05-22-2024 12:40 PM |
User | Count |
---|---|
102 | |
68 | |
66 | |
51 | |
41 |
User | Count |
---|---|
164 | |
84 | |
68 | |
66 | |
61 |