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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

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