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
M_J_Farrow
Advocate II
Advocate II

Max of a Measure

Hi. I have a number of datasets which I am using in my Power BI model which can be simplified to the below for the purposes of this question:

 

Staff MemberStaff ManagerHoursProductive Hours
Staff Member AManager 1108
Staff Member BManager 21513
Staff Member CManager 31010
Staff Member DManager 22018
Staff Member EManager 11513

 

 

the only thing to note about the above is that the staff manager name comes from a parent table which contains various types of information about individual staff members which can be propogated to my main dataset via a 1 to many relationship using a unique staff member identifier that exists in the parent table and my main dataset.

 

I have created a simple measure (% Productivity) which divides productive hours by total hours. Obviously this creates different results in different filter contexts. However, what I want to do is find the max of % productivity when my data is aggregated at staff manager level. In the above example the table I want to calculate the MAX on is this:

 

MangerHoursProductive Hours% Productivity
Manager 110880.00%
Manager 2353188.57%
Manager 3252392.00%

 

Therefore I want a measure that, whatever the filter context, will always return the max productivity at manager level (in this case 92%.

 

Any help would be much appreciated.

 

Thanks!

1 ACCEPTED SOLUTION

@M_J_Farrow,

Please check my PBIX file.
https://1drv.ms/u/s!AhsotbnGu1Nok0x6nUs3Pv_bM-aU

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yuezhe-msft
Employee
Employee

@M_J_Farrow,

Create the following column and % Productivity measure in your table.

Column = CALCULATE( SUM(Table[Productive Hours]),FILTER(Table,Table[Staff Manager]=EARLIER(Table[Staff Manager])))/CALCULATE( SUM(Table[Hours]),FILTER(Table,Table[Staff Manager]=EARLIER(Table[Staff Manager])))
% Productivity = CALCULATE(MAX(Table[Column]),ALL(Table))

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thanks for this Lydia. My data isn't arranged as you have shown in your graphic. When I showed the data in this format it was a visual I create from my data. Where would I therefore add the calculated column- to the data by staff member? In this data set you can have more than one hours booking per staff member.

@M_J_Farrow,

Please check my PBIX file.
https://1drv.ms/u/s!AhsotbnGu1Nok0x6nUs3Pv_bM-aU

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Lydia. Sorry I took so long to reply to this. Must have missed your reply first time around.

M_J_Farrow
Advocate II
Advocate II

There is a minor error in the above. In the first gtable staff member E should show as managerr 3. Doesn't impact the question which I'm asking.

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.