Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
joannasokolowsk
Helper II
Helper II

MAX/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. 

 

namecategorycountrygradetraining titlelength in hours
ASoft skillPolandDirectorCommunciation skills5
ASoft skillPolandDirectorNegotiation skills6
ASoft skillPolandDirectorWriting7
ASoft skillPolandDirectorPresentation skills9.5
ATechnicalPolandDirectorExcel10
ATechnicalPolandDirectorWord11
ATechnicalPolandDirectorPower Point0.5
BSoft skillItalyAssociateCommunciation skills5
BSoft skillItalyAssociateNegotiation skills6
BTechnicalItalyAssociateExcel10
BTechnicalItalyAssociateWord11
1 ACCEPTED SOLUTION

@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])

View solution in original post

5 REPLIES 5
joannasokolowsk
Helper II
Helper II

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...

 

joannasokolowsk_0-1612775697555.png

 

@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])

Perfect! it works!!! 

 

Have a great day!

amitchandak
Super User
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-measures

TOPN 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/3...

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 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...

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.