Forum Discussion
Using rank as filter
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!
17 Replies
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:
Rank = RANKX(ALL(Table[Channel]),[Total visits])where [Total visits] is a measure sum(visits)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])
