Forum Discussion

bo_afk's avatar
bo_afk
Post Patron
5 years ago

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...
Need Help
camargos88's avatar
camargos88
Community Champion
5 years ago

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's avatar
bo_afk
Post Patron
5 years ago

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's avatar
    camargos88
    Community Champion
    5 years ago

    bo_afk ,

     

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

    • bo_afk's avatar
      bo_afk
      Post Patron
      5 years ago

      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's avatar
        camargos88
        Community Champion
        5 years ago

        bo_afk ,

         

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

        Just trying to understand to think about the measure.