Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 = ...
Solved! Go to 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
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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |