Creating a calculated table based off of a column and a measure

[DorienM]

I am trying to create a table based off of a column from one table and a measure but this is new territory for me. Here is how my data is structured:

I have one table with IP address to Company name mappings as shown here: ('ip_mappings')

IP	Company Name
1.2.3.4	Org A
1.2.3.5	Org A
2.22.222.2	Org B
123.221.98.67	Org C
22.67.251.2	Org D
192.53.98.76	Org D

A second table tracks visits to a website ('website_visits')

Visit ID	Visit Time	IP Address
0	08/22/2021	1.2.3.4
1	08/24/2021	123.221.98.67
2	08/25/2021	2.22.222.2
3	08/28/2021	2.22.222.2
4	08/30/2021	1.2.3.4
5	08/31/2021	1.2.3.5
6	09/01/2021	22.67.251.2
7	09/04/2021	192.53.98.76
8	09/06/2021	192.53.98.76

A relationship connects the two tables by IP address so that IP addresses can get mapped to Org names, in reality the mapping table contains 100,000+ rows and the visits table contains 1M+ rows. I need to put Count of Visits on a pie or donut chart with Company Name, naturally this is pretty un managable with 100K+ companies, to make it useful I need to display the Top 10 visiting companies with a dynamic "Others" category, filtering by Top N acomplishes the first part of this but it does not get me an others category.

To acomplish this I think I need to create a third calculated table with three columns, the first should be ip_mappings[Company Name], the second should be a measure [Count of Website Visits], and the third an if statement using RANKX, something like this:

IF(RANKX(Table3,Table3[Count of Website Visits])<=2,Table3[Company Name],"Others")

Ideally the table would come out looking something like this:

Company Name	Count of Website Visits	Category Name
Org A	3	Org A
Org B	2	Others
Org C	1	Others
Org D	3	Org D

Using Category Name in the pie or donut chart I could display the top N results with an others category.

So I have two questions:

1. Is this the 'Correct' way to go about this? Is there a better way to achive Top N with a dynamic "Others" category, bonus points if I can drill down through "Others" N results at a time

2. How do I actually create this table? I have never done a full calculated table before so I am lost when it comes to what DAX to put after Table3 = ...

[v-easonf-msft · Accepted solution]

Hi,  @DorienM 

As your category name field needs to be applied to the pie chart, it is not recommended to use measure. Please try the following formula(calculated table):

Table3 =
VAR tab1 =
    SUMMARIZE (
        website_visits,
        ip_mappings[Company Name],
        "Count_of_visit ID", COUNT ( website_visits[Visit ID] )
    )
VAR tab2 =
    ADDCOLUMNS ( tab1, "Rankx", RANKX ( tab1, [Count_of_visit ID],, DESC, SKIP ) )
VAR tab3 =
    ADDCOLUMNS (
        tab2,
        "Category Name", IF ( [Rankx] <= 2, [Company Name], "Others" )
    )
RETURN
    tab3

Best Regards,
Community Support Team _ Eason

[MFelix]

Hi @DorienM,

Check the link below with a possible option to solve your issue.

https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/