Forum Discussion
Helper IIMAX/MIN measure
Hi all,
I need a help with MAX/MIN measure which will be checking the max/min sum of number of hours per person (by category).
For example, based on below datasat the max sum number of hours per person for soft skills category should be displayed as 27.5 and min sum number of hour per person for technical category should be 21. Also there will be few slicers on the page (like grade, country etc), so the min/max number should change accordingly to filters applied.
Please help me with creating this min/max measure measure.
| name | category | country | grade | training title | length in hours |
| A | Soft skill | Poland | Director | Communciation skills | 5 |
| A | Soft skill | Poland | Director | Negotiation skills | 6 |
| A | Soft skill | Poland | Director | Writing | 7 |
| A | Soft skill | Poland | Director | Presentation skills | 9.5 |
| A | Technical | Poland | Director | Excel | 10 |
| A | Technical | Poland | Director | Word | 11 |
| A | Technical | Poland | Director | Power Point | 0.5 |
| B | Soft skill | Italy | Associate | Communciation skills | 5 |
| B | Soft skill | Italy | Associate | Negotiation skills | 6 |
| B | Technical | Italy | Associate | Excel | 10 |
| B | Technical | Italy | Associate | Word | 11 |
joannasokolowsk , Try one of the two
minx(values(Table[name]), calculate(sum(Table[length in hours])))
or
minx(Summarize(Table,Table[category],Table[name]"_1", calculate(sum(Table[length in hours]))),[_1])
5 Replies
- amitchandak
Super User
joannasokolowsk , You can try measure like these
minx(values(Table[category]), calculate(sum(Table[length in hours])))
maxx(values(Table[category]), calculate(sum(Table[length in hours])))
For top Min or Max, you can use Rank or TOPN
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measuresTOPN https://www.youtube.com/watch?v=QIVEFp-QiOk
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
joannasokolowskHey!
Thank you for a such quick response! I added those measure but the result is not as expected ;/
For example for soft skills category the max should be 27.5 as an "A" person has the highest sum number of hours for this category (person "B" has 11 hours for soft skills so it is smaller). When I apply this measure I see 38.5 which is the sum of total hours...
- joannasokolowsk
Hey,
Thank you for a such quick response! I added those measure but the result is not as expected ;/
For example for soft skills category the max should 27.5 as an "A" person has the highest sum number of hours for this category (person "B" has 11 hours for soft skills so it is smaller). When I apply this measure I see 38.5 which is the sum of total hours...
- amitchandak
joannasokolowsk , Try one of the two
minx(values(Table[name]), calculate(sum(Table[length in hours])))
or
minx(Summarize(Table,Table[category],Table[name]"_1", calculate(sum(Table[length in hours]))),[_1])- joannasokolowsk
Perfect! it works!!!
Have a great day!