Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a table below which shows the number of visits to a web site for each customer based on the channel. I've also created a measure to rank the channel for each customer (using RANKX).
What I want to do is count how many customers there are for each channel where rank is equal to 1.
Table with ranks:
Customer | Channel | Total visits | Rank |
A | Paid Search | 43 | 1 |
A | Direct Load | 3 | 2 |
A | 2 | 3 | |
A | Natural Search | 1 | 4 |
A | Referring Sites | 0 | 5 |
B | Direct Load | 26 | 1 |
B | Paid Search | 5 | 2 |
B | 0 | 3 | |
B | Referring Sites | 0 | 3 |
C | Paid Search | 16 | 1 |
C | Direct Load | 3 | 2 |
C | Natural Search | 1 | 3 |
C | Referring Sites | 1 | 3 |
D | Direct Load | 2 | 1 |
D | Paid Search | 1 | 2 |
E | Paid Search | 4 | 1 |
E | Direct Load | 1 | 2 |
E | Paid Social | 1 | 2 |
Filter for where Rank = 1
Customer | Channel | Rank |
A | Paid Search | 1 |
B | Direct Load | 1 |
C | Paid Search | 1 |
D | Direct Load | 1 |
E | Paid Search | 1 |
EXPECTED RESULT: Count number of customers per channel where rank = 1
Channel | Customer Count |
Paid Search | 3 |
Direct Load | 2 |
Can anyone help with how I can do this? When I tried this by putting the measure filter on the table (rank=1), only 'Paid search' appeared and I think this is because it has the highest total visits overall.
Thanks!
Hi @camargos88, thanks for your response.
I did try to filter the visual but it didnt work for me. What formula did you use for Rank?
I used the following:
@bo_afk ,
Use this measure, so you don't need to filter the visual:
_Rank Channel =
VAR _tbl = SUMMARIZE('Table', 'Table'[Channel], 'Table'[Customer], "Rank", RANKX(FILTER(ALL('Table'), 'Table'[Customer] = EARLIER('Table'[Customer])), CALCULATE(SUM('Table'[Total visits])),, DESC))
RETURN COUNTX(FILTER(_tbl, [Rank] = 1), [_Rank])
Thanks @camargos88 for your suggestion!
I have another column in my data, "device type", so it's possible for channel to appear on multiple rows for each customer if they browse on several devices. How do I amend the formula to take this column into account?
Hi @camargos88 ,
Eventually I want to show this in a stacked column chart with customer counts as the value and 'channel' as the legend.
@bo_afk ,
So what role the column "device type" would play here ?
Just trying to understand to think about the measure.
Hi @camargos88, the issue I have is the following:
I am trying to rank on the Channel only and not device type. So 'direct load' has total of 2 visits for this customer and therefore it should be ranked 1 and 'paid search' should then be ranked 2 but as you see below, they're both ranked 1.
When I copied your formula (count customers with rank 1) it shows the following, which is wrong because as mentioned above, only direct load should appear here.
Any ideas?
Thanks again!
Hi @camargos88, see below the data
Customer | Channel | Device | Total visits |
A | Direct Load | Tablet | 3 |
A | Mobile Phone | 2 | |
A | Natural Search | Tablet | 1 |
A | Paid Search | Tablet | 43 |
B | Direct Load | Desktop | 17 |
B | Direct Load | Tablet | 9 |
B | Paid Search | Desktop | 3 |
B | Paid Search | Tablet | 2 |
C | Direct Load | Tablet | 3 |
C | Natural Search | Tablet | 1 |
C | Paid Search | Desktop | 8 |
C | Paid Search | Tablet | 8 |
C | Referring Sites | Tablet | 1 |
D | Direct Load | Mobile Phone | 1 |
D | Paid Search | Desktop | 2 |
D | Paid Search | Mobile Phone | 2 |
D | Paid Social | Desktop | 1 |
E | Direct Load | Desktop | 1 |
E | Direct Load | Mobile Phone | 1 |
E | Paid Search | Mobile Phone | 1 |
Hi @camargos88, thanks for the file!
Numbers are still slightly off and I think this is because of customer E. As you can see below, the total number of visits for "Direct Load" for this customer is 2 (1 on mobile and 1 on desktop), but the total visits for "Paid search" is 1 (just 1 for mobile). Therefore, "Direct load" should have a rank of 1 and "Paid search" a rank of 2, but here both are ranked as 1.
Is theere a way to 'ignore' the device type?
@bo_afk ,
Direct Load has customer with 1 for customer B and E.
I am a little bit confused here:
"Therefore, "Direct load" should have a rank of 1 and "Paid search" a rank of 2, but here both are ranked as 1."
The measure gets the highest visits for each channel/customer and filter the ranking number 1.
What do you mean with rank of 2 ?
@camargos88 Sorry I should say that "Paid search" shouldn't have a rank of 1 because the total number of visits for customer E through this channel is 1, whereas the total number of visits for customer E through "Direct load" is 2. Does this make sense?
Hi @camargos88, thanks for this!
Weirdly, it doesnt seem to work with my dataset and I'm not too sure why...it's still showing rank 1 for customer E for both 'direct load' and 'paid search'.
But I can definitely see that it's worked with the one I gave you. Maybe I need to reload my data back into my report or something. Will try to figure out what's caused this.
@bo_afk ,
Keep in mind that if you have the ties, the values are gonna be the same, so you need to break it.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
26 |
User | Count |
---|---|
97 | |
86 | |
43 | |
40 | |
35 |