Using rank as filter

[bo_afk]

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	Email	2	3
A	Natural Search	1	4
A	Referring Sites	0	5
B	Direct Load	26	1
B	Paid Search	5	2
B	Email	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!

[camargos88]

@bo_afk ,

You just need to filter the visual:

[bo_afk]

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)

[camargos88]

@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])

[bo_afk]

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?

[camargos88]

@bo_afk ,

How do you want to show this information (visual) ?

[bo_afk]

Hi @camargos88 ,

Eventually I want to show this in a stacked column chart with customer counts as the value and 'channel' as the legend.

[camargos88]

@bo_afk ,

So what role the column "device type" would play here ? 

Just trying to understand to think about the measure.

[bo_afk]

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!

[camargos88]

@bo_afk ,

Can you send me the mock data with this new column ? So I can work on this measure.

[bo_afk]

Hi @camargos88, see below the data

Customer	Channel	Device	Total visits
A	Direct Load	Tablet	3
A	Email	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

[camargos88]

@bo_afk ,

Please, check the attached file.

[bo_afk]

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?

[camargos88]

@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 ?

[bo_afk]

@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?

[camargos88]

@bo_afk ,

Check the file again, I've ignored the device type.

[bo_afk]

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.

[camargos88]

@bo_afk ,

Keep in mind that if you have the ties, the values are gonna be the same, so you need to break it.