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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee
Microsoft 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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.