Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |