cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DorienM
Helper II
Helper II

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

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')

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

 

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

Visit IDVisit TimeIP Address
008/22/20211.2.3.4
108/24/2021123.221.98.67
208/25/20212.22.222.2
308/28/20212.22.222.2
408/30/20211.2.3.4
508/31/20211.2.3.5
609/01/202122.67.251.2
709/04/2021192.53.98.76
809/06/2021192.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 NameCount of Website VisitsCategory Name
Org A3Org A
Org B2Others
Org C1Others
Org D3Org 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 = ...

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-easonf-msft
Community Support
Community Support

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

View solution in original post

MFelix
Super User
Super User

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/

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors