Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
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)
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)
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |