Forum Discussion

M_J_Farrow's avatar
M_J_Farrow
Icon for Advocate II rankAdvocate II
8 years ago
Solved

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!

Need Help

5 Replies

  • M_J_Farrow's avatar
    M_J_Farrow
    Icon for Advocate II rankAdvocate II
    8 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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



    Regards,
    Lydia

    • M_J_Farrow's avatar
      M_J_Farrow
      Icon for Advocate II rankAdvocate II
      8 years ago

       

      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.