Creating a table with top 10 locations for each category

[Anonymous]

I'm looking to make a table (or maybe a matrix) visualization that has columns with each ticket category from our service desk. Under each column, I want to show the top 10 locations in terms of the amount of tickets that fit each ticket category. We also need to show the amount of tickets beside each location. So it's kind of like a hybrid between a table and matrix.

Example:

Billing	Contracts	Customers	General	Loyalty	Misc.	Management	Other	Payments	Service
Annapolis (203)	Philadelphia (83)	etc.	etc.
Baltimore (198)	Raleigh (38)
Philadelphia (128)	San Francisco (34)
San Francisco (120)	etc.
Seattle (74)	etc.
Raleigh (48)
Boise (38)

All the data is in my table but I just have no idea how to get it to display in this manner.

Thanks!

[Anonymous]

Hi @Anonymous ,

According to my understanding, you want to display top10's Location and Amount of each category,right?

I customed some data shown below and then added a rank column using the following formula:

Rank =
RANKX (
    FILTER ( 'Table', 'Table'[Category] = EARLIER ( 'Table'[Category] ) ),
    [Amount],
    ,
    DESC,
    DENSE
)

The final data sample looked like this: ( I would take Top5 as example:)

To fulfill your requirement, I suggest you create a new table with rank (1-5) column:

Then use LOOKUPVALUE() function to find matched Location and Amount:

Billing =
LOOKUPVALUE (
    'Table'[Locations ],
    'Table'[Rank], [Rank],
    'Table'[Category], "Billing"
) & "("
    & LOOKUPVALUE (
        'Table'[Amount],
        'Table'[Rank], [Rank],
        'Table'[Category], "Billing"
    ) & ")"

The final output looks like this:

Please kindly take a look at my pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you again! And also thank you to @AllisonKennedy as I wasn't understanding the first steps.

I'm not 100% sure but I think I may be running into an issue becuase there are locations that tie for the same amount of tickets, therefore they're getting the same rank...

Here is the "Contracts" category in my table...

So I'm getting this error when trying to create that column in the rank table...

The reason I believe this is because before this I created a "Billing" column which only has one location with one ticket, therefore it gave me no errors since there was nothing to rank.

Any ideas on what I can do about this?

Thanks!

[Anonymous]

Hi @Anonymous ,

The error is caused by multiple values(rank).

You could use the following formula to rank when there are duplicate values:

rank =
RANKX (
    'Table',
    RANKX ( 'Table', [Count],, ASC, DENSE ) * 100
        + RANKX ( 'Table', [Location],, ASC, DENSE ),
    ,
    DESC,
    DENSE
)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

So the difference I'm running into is that I only have a column for the ticket ID itself. I do not have an "amount" column. Every row is based on a specific ticket. 

By using the "Enter Data" option, will that create a visual that reflects the table you have for the final output?

I apologize, I am extremely novice at Power BI and unfortunately, the only way I learn is by doing specific things. I've tried to follow guides but I just have trouble mentally translating examples to my specific data. Thank you so much for your help. 

[StefanoGrimaldi]

hey did you try with the Dax I sended you earlier?

Billing Table =

var SummaryTable = SUMMARIZE(Billing,Billing[Billing Location],Billing[Contract Location],"Billing Qauntity",COUNT(Billing[Billing Location]),"Contract Location Qauntity",COUNT(Billing[Contract Location]))

var top10table = TOPN(10,SummaryTable,[Billing Qauntity])

return top10table

it will make a new table create the columns you needing and sort by the amount of billings count and get you only the 10 with most billing quantity by count of bills. them you just need to concatenate a few columns to get the column you wanted of location + quantity. 

[AllisonKennedy]

@Anonymous  to implement @Anonymous  's solution you can create a new table using DAX that counts your tickets: 

Table = SUMMARIZECOLUMNS( Table[Issues With] , Table[Marina Location], "TicketCount", COUNT(Table[Id]) )

Then you should be able to follow the solution by Evelyn. 

Let us know what further questions you have. 

[Anonymous]

I'm so sorry, please disregard my last message. Somehow I missed the final portion of your post! I see that actually is exactly what I've been looking for.  Let me give this a shot!

[Anonymous]

Thank you! The only difference is that I need the categories (Billing, Contracts, Etc.) as the column headers. I need the top 10 locations sorted by the amount of tickets and the amount of tickets underneath thse columns.

I followed the advice of @AllisonKennedy and created a matrix for each category to form a makeshift table by placing them all side by side.  I'm running into a couple of issues though...

First issue - I need to get rid of those column headers (Billing/Billing, Contracts/Contracts, etc.) under the matrix title.  OR I need to at least be able to change them. I'm able to change the first column by renaming the "Rows" field but I'm unable to find where to change the second column header so it can say "Tickets", this is the column with the ticket amounts.

Second issue - I'm not goingto be able to fit all of my categoris straight across on the page. If I were able to make one big table/matrix, I would have a scroll bar which would be sufficient but with the way I'm doing this, I'm forced to try to fit all of the matrixes horizontally with no scrolling.  I thought about going down below to continue the matrixes but we really need these all straight across so it at least mimics a full table. 

Any ideas on either of these?

[amitchandak]

@Anonymous , I have seen this video some time back, it does the data display like this. Have a look if this can help

https://www.youtube.com/watch?v=WSnB5y2JXv8

[StefanoGrimaldi]

Hey,

let’s see, let’s say you have a table of data like this I can assume from your example:

 with other data in more columns

You can do this by various approach, I will suggest a new table based on that initial data, to summarize the information you need for the visual:

New table:

Billing Table =

var SummaryTable = SUMMARIZE(Billing,Billing[Billing Location],Billing[Contract Location],"Billing Qauntity",COUNT(Billing[Billing Location]),"Contract Location Qauntity",COUNT(Billing[Contract Location]))

var top10table = TOPN(10,SummaryTable,[Billing Qauntity])

return top10table

Result:

Them add some columns to match the first two of your examples:

Billing = 'Billing Table'[Billing Location] & "(" & 'Billing Table'[Billing Qauntity] & ")"

Contracts = 'Billing Table'[Contract Location] & "(" & 'Billing Table'[Contract Location Qauntity] & ")"

Result:

Then configure your table (example without format):

Note: sort the Billing column by the billing quantity column to have the order you need in the visual,

I hope this example its of any use to your need, If you found this post helpful, please give Kudos.

[AllisonKennedy]

@Anonymous  Your example is in a strange format - is that what it looks like in the raw data table? Do you have a column for TicketID or Number of Tickets? Is the number in () the number of tickets for each region? You need that in a column by itself, either direct from the data or using Split Column in Power Query. 

Then you can filter a visual (such as matrix) for top 10: https://docs.microsoft.com/en-us/microsoft-365/admin/create-groups/manage-groups?view=o365-worldwide

Change the filter on Location in your matrix to Top N, type 10, drag the [Amt of Tickets] column that we created or found above from the fields pane in far right to the box in the filter. 

Click Apply. 

[Anonymous]

There is a column for Ticket ID, which I've used to make top 10 filters for in a different visualization, a single list based on a slicer (with the categories listed there) but my boss wants a table with a list of the top 10 locations (ticket amount) underneath each category.  The number in () is the amount of tickets for that specific location for that specific category (the column headings).

When I've tried what I think you're telling me to do pertaining to the top 10 filter, I get a table as shown in the screenshot below. But I need it to look like my example instead.  

[AllisonKennedy]

@Anonymous Ah, ok. I see what you mean now. You can't do this in a single visual. You'll need to create a separate visual for each column unfortunately. Then you can fitler for top 10 locations in each visual.