How to show Top Categories by tickets by individual months

[Viperchick007]

Hello, 

I am trying write a DAX or Power Query to show the top 5 Categories of tickets in each individual months for a Bar Chart and a Pivot Table. There are several filters set already on this page and visual. 

[SacheeTh · Accepted solution]

Hi @Viperchick007,

There are several methods to get the top 5 categories, Mest way is to get using the DAX, but since this is added under the Power Query will give you that method,
Using Power Query

1. Sort Data by Ticket Count:

   • In Power Query, group your data by Month and Category.
   • Summarize the ticket count for each combination.
   • Sort the data by Month, then Ticket Count in descending order.

2. Add a Rank Column:

   • Use the Index Column feature to rank categories within each month after grouping.
   • For example:
     • Group by Month, then add an index for each category within the group.

3. Filter for Top 5:

   • Filter the data to include only rows where the rank is less than or equal to 5.

4. Load Data into Power BI:

   • Use this filtered data in your Bar Chart and Pivot Table.

---

Additional Considerations

• Dynamic Filtering: If your data needs to update dynamically based on filters, the DAX solution is better because it adjusts in real-time.
• Performance: If your dataset is large, pre-filtering in Power Query might improve performance.

To show the Top 5 Categories of tickets by individual months in Power BI for a Bar Chart and a Pivot Table, follow these steps:

---

Using DAX

1. Create a Measure for Ticket Count: Calculate the total tickets for each category. For example:

   Total Tickets = COUNTROWS(TicketsTable)

2. Create a Ranking Measure: Use RANKX to rank the categories by ticket count within each month.

   Category Rank =
   RANKX(
       ALLSELECTED(TicketsTable[Category]),
       CALCULATE([Total Tickets]),
       ,
       DESC,
       Dense
   )

   • Replace TicketsTable[Category] with the column for your categories.
   • ALLSELECTED ensures the ranking respects slicers and filters applied on the page.

3. Filter for Top 5 Categories: Add a calculated column or use a visual-level filter to limit the data to the top 5 categories:

   Is Top 5 =
   IF([Category Rank] <= 5, 1, 0)

   Use this as a filter for your visual, setting Is Top 5 = 1.

4. Set Up the Visuals:

   • For the Bar Chart:
     • Add Category on the X-axis and Total Tickets on the Y-axis.
     • Apply a filter for Is Top 5 = 1.
   • For the Pivot Table:
     • Add Month and Category to rows, and Total Tickets to values.
     • Apply the same filter for Is Top 5 = 1.

Let me know if you need assistance implementing these!

[Viperchick007]

Thank you! I used DAX. It has worked for creating a Pivot Table which displays different categories in each month as opposed to using Top N filter that shows the same categories in each month and I know they should all be different.

I have not found a solution to the bar chart but the table is sufficient for now.