Forum Discussion
TopN - Rows increase when adding a column
Hello,
I am trying to calculate the TOP 5 Customers (by considering the Tot Sales) which have negative delta vs profit average. This is working with the following measure:
As you can see above, I need to keep the filtering on Year, Category, Scenario and Country.
Also, I need to see the TOP 5 Customers which have negative delta vs profit average and the related categories. However, when I use the same measure and I add the Categories field in my matrix, Power Bi is showing me the TOP 5 Customers (based on Sales) for each category.
I want to have a result like this:
| Customer | Cat 1 | Cat 2 | Cat 3 |
Alpha | -1% | -1% | -5% |
| Beta | -2% | -4% | -1% |
| Gamma | -1% | -2% | -1% |
| Delta | -3% | -3% | -1% |
| Teta | -2% | -2% | -1% |
Do you have any ideas on how to display only TOP 5 Customers and show the Categories in the columns?
Thank you for your reply.
Unfourtunatly it does not work. I cannot add a column from another table with ADDCOLUMNS, I need to have a measure for the new column.
I fixed the issue by creating a new calculated table which is taking only the relevant columns for the calculations and grouping. Then, I created some measures for the calcualtions.
It's not an efficiendt way but it works (I was hoping to do everything in one measure by leveraging the VARIABLES)
2 Replies
Hi artur4 ,
Based on the description, please try the following dax formula.Top5CustomersCategories = VAR CustomerTable = ADDCOLUMNS( SUMMARIZE( FILTER(ALLSELECTED('DATA'[Customer Name]), [GM% vs Country AVG] < 0), 'DATA'[Customer Name], "DeltaGM", [DeltaGM] ), "Category", 'DATA'[Category] ) RETURN TOPN(5, CustomerTable, [DeltaGM], DESC)If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.
How to provide sample data in the Power BI Forum
Best Regards,
Wisodm Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your reply.
Unfourtunatly it does not work. I cannot add a column from another table with ADDCOLUMNS, I need to have a measure for the new column.
I fixed the issue by creating a new calculated table which is taking only the relevant columns for the calculations and grouping. Then, I created some measures for the calcualtions.
It's not an efficiendt way but it works (I was hoping to do everything in one measure by leveraging the VARIABLES)
