Forum Discussion
Top1 measure
Hi, I'm looking to get the Top1 based on the IT_Issue with the highest difference. Here is my sample data:
| Location | Based | IT_Issue | 2023-2024 | 2024-2024 | Difference |
| London | Local Office | Laptop | 4 | 37 | 33 |
| London | Local Office | 6 | 10 | 4 | |
| London | Local Office | Printer | 8 | 9 | 1 |
| Manchester | Local Office | Laptop | 12 | 36 | 24 |
| Manchester | Local Office | 7 | 17 | 10 | |
| Manchester | Local Office | Printer | 4 | 10 | 6 |
| London | Field | Laptop | 2 | 6 | 4 |
| London | Field | 1 | 2 | 1 | |
| London | Field | Printer | 2 | 4 | 2 |
| Manchester | Field | Laptop | 8 | 5 | -3 |
| Manchester | Field | 12 | 16 | 4 | |
| Manchester | Field | Printer | 10 | 19 | 9 |
| London | Central Services | Laptop | 18 | 29 | 11 |
| London | Central Services | 11 | 11 | 0 | |
| London | Central Services | Printer | 6 | 8 | 2 |
| Manchester | Central Services | Laptop | 13 | 7 | -6 |
| Manchester | Central Services | 7 | 19 | 12 | |
| Manchester | Central Services | Printer | 2 | 4 | 2 |
This is what I need:
| IT_Issue | Location | Based | Type | Difference |
| Laptop | London | Local-Office | Laptop | 33 |
| Manchester | Central Services | 12 | ||
| Printer | Manchester | Field | Printer | 9 |
Thanks
Hi RichOB
Can you please try the below DAX?Create a new table and use the below DAX, which returns a table
TopITIssuesByDifference =VAR RankedTable =ADDCOLUMNS('Table', -- Replace with your actual table name"Rank",RANKX(FILTER('Table', 'Table'[IT_Issue] = EARLIER('Table'[IT_Issue])),'Table'[Difference],,DESC,DENSE))RETURNSELECTCOLUMNS(FILTER(RankedTable, [Rank] = 1),"IT_Issue", [IT_Issue],"Location", [Location],"Based", [Based],"Type", [IT_Issue],"Difference", [Difference])
If this answers your questions, kindly accept it as a solution and give kudos.
3 Replies
VAR maxDiff=
Calaculate(
Max( table[difference]),
ALLEXCEPT( table, table[location])
)
Return
Countrows(
Filter(
Table,
Table[difference] = maxDiff
)
)
Add this to the filter pane for the table visual, and set to where count >0
Hi RichOB
Can you please try the below DAX?Create a new table and use the below DAX, which returns a table
TopITIssuesByDifference =VAR RankedTable =ADDCOLUMNS('Table', -- Replace with your actual table name"Rank",RANKX(FILTER('Table', 'Table'[IT_Issue] = EARLIER('Table'[IT_Issue])),'Table'[Difference],,DESC,DENSE))RETURNSELECTCOLUMNS(FILTER(RankedTable, [Rank] = 1),"IT_Issue", [IT_Issue],"Location", [Location],"Based", [Based],"Type", [IT_Issue],"Difference", [Difference])
If this answers your questions, kindly accept it as a solution and give kudos.Hi RichOB,
By using the DAX below, you can achieve your desired result of getting the Top 1 IT_Issue with the highest Difference across each category:
Top1_Issues =
FILTER (
ADDCOLUMNS (
'IT_Issues',
"MaxDiff", CALCULATE (
MAX('IT_Issues'[Difference]),
ALLEXCEPT('IT_Issues', 'IT_Issues'[IT_Issue])
)
),
[Difference] = [MaxDiff]
)You can use the resulting Top1_Issues table in a visual by adding the fields:
IT_Issue, Location, Based, Type (add as a new column: Type = [IT_Issue]) &Difference
Let me know if you want it as a measure instead of a table!
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀[Explore More]
