Forum Discussion
Helper INeed help with a measure
Hi all. I'm quite new to Power BI so I hope this isn't a stupid question.
This table is a simplified version of what I am dealing with:
| Project Number | Work Ordered by Team | Work Done by Team | Hours Worked |
| 1 | Team A | Team B | 5 |
| 2 | Team A | Team C | 15 |
| 3 | Team A | Team C | 36 |
| 4 | Team A | Team C | 43 |
| 5 | Team A | Team B | 21 |
| 6 | Team B | Team A | 44 |
| 7 | Team B | Team A | 52 |
| 8 | Team B | Team A | 21 |
| 9 | Team B | Team C | 12 |
| 10 | Team C | Team B | 35 |
| 11 | Team C | Team B | 22 |
| 12 | Team C | Team A | 2 |
I would like to know:
Which team ordered the most work in total?
Which team did the most work in total?
Which team did most work for Team A?
Which team ordered most work for Team B?
I was thinking that this is simplest to do using measures and a slider to choose between teams. However I haven't figured out how.
Thanks for the help.
Jonas
Which team ordered the most work in total?
Measure = VAR __Table = SUMMARIZE('Table',[Work Ordered by Team],"Hours",SUM([Hours Worked])) VAR __Max = MAXX(__Table,[Hours]) VAR __Team = MAXX(FILTER(__Table,[Hours] = __Max),[Work Ordered by Team]) RETURN __TeamThe rest are going to be variations on that theme. This is an example of Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
3 Replies
- Greg_Deckler
Community Champion
Which team ordered the most work in total?
Measure = VAR __Table = SUMMARIZE('Table',[Work Ordered by Team],"Hours",SUM([Hours Worked])) VAR __Max = MAXX(__Table,[Hours]) VAR __Team = MAXX(FILTER(__Table,[Hours] = __Max),[Work Ordered by Team]) RETURN __TeamThe rest are going to be variations on that theme. This is an example of Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
J_o_n_a_sThank you Greg_Deckler and amitchandak.
I found Greg's explanation easier to understand and implement. For question 3 and 4 i then just used a slider to filter from which team's perspective I want to see the results.
- amitchandak
Super User
J_o_n_a_s , you sum Hour worked by Work Ordered by Team ,Work Done by Team.
You can use visual level filter top N on Hour worked.
Other is use Rank and Top N filter
3 and 4 the are either visual level filter or slicer or create a measure
calculate(sum(Table[Hour worked]),Table[Hour worked]="A")
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
