cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Calculation of two total values that are percentages

Hi there,

 

I have a unique challenge (at least for me) that I can't figure out. It involves creating a calculated measure of 2 total values that are both percentages. I will try to explain:

 

  • I have a visual that represents a Manager's team with some hours, rates, and utilization information.
  • We are calculating each employee's Utilization as the Sum of Direct (Billable) Hours / Base hours. Base hours is static per month as Employee's hours/day * working days in a month e.g. 165
  • Each employee also has a Taregt Utilization which is a fixed value
  • For Utilization total I use a IF(HASONEVALUE(.... to determine if it's a detail line (standard calc) or on Total line we divide the the Utilization by the number of rows to get the proper Utilization % for the team
  • For Utilization Target column I do something similar except on detail rows I take the static value for the employee (fixed into every row of the data) and on Total line I do a Sum of the Target Utilizations / row count to get the average Target.

The result looks like this:

KevinColes_2-1594164144982.png

This is exactly what we want and all is good. What I now want is to create a new Calculated Measure called "Utilization Rate to Target which would be Utilization Total / Target Utilization Total or as in the above example 62% / 88% = 70%. The added kicker is I need to use this in a completely different visual.


Is it possible to divide these 2 totals somehow?


TIA,


Kevin Coles

3 REPLIES 3
Highlighted
Super User VI
Super User VI

Re: Calculation of two total values that are percentages

Please try this approach

 

Pct of Target Utilization =
AVERAGEX (
VALUES ( Table[Staff Name] ),
DIVIDE ( [Utilization], [Target Utilization] )
)

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Calculation of two total values that are percentages

Hi @mahoneypat,

 

Thank you for the suggestion but I don't think this will work. I will give a bit more detail into the Utilization Calc.....each Employee Row is a summation of time card records. This is where the Direct Hours come from. So if you take the first row in my screen shot....the employee has many records in the given period that total 122 Direct Hours. The base hours is fixed for the period for all employees (7.5 hrs * 22 working days for the particular period I've shown). Direct (122) / Base (165) = 74% Utilization. But in order to get the proper average shown on the total line I actually calculate this column this way:

 

Utilization = IF(HASONEVALUE('myTable'[EmployeeName]),
DIVIDE(
    SUM('myTable'[DirectHrs]),
    SUM('Base Hours by Pd'[BaseHrs]),0), // I use a Periods table to get the proper base hours for each period

DIVIDE(
DIVIDE(
     SUM('myTable'[DirectHrs]),
     SUM('Base Hours by Pd'[BaseHrs]),0),
DISTINCTCOUNT(myTable[EmployeeName])
)
)
 
This basically does the Direct divided by the appropriate Base Hrs for the given month on detail rows and on Total it takes the sum of all Direct Hours / sum of the Base Hours values and then divides by the number of distinct Employee Rows. If multiple periods are selected base hours go up accordingly because of the Base Hours by Pd table that I link to.
 
For Target Utilization each row in the table has the Employee's static Target Utilization rate which seldomly changes. I do a similar calculation as above whereby on Detail Rows I simply take the MAX('myTable'[EmployeeName]) because the value is always static. And on the Total row I do SUM(myTable[TargetRatio]) / COUNTROWS('myTable').
 
Both of these calculations work perfectly as I want, including when multiple Periods are selected. Now I need to divide the 2 as a new measure for use in a separate visual.
 
So I need to get 66% / 88% = 70% into a new measure.

Thanks,

Kevin
 
Highlighted
Helper I
Helper I

Re: Calculation of two total values that are percentages

OK I was able to solve this by extrapolating my two calculations into one larger calculation. What I forgot was that the 2 visuals I have a filtered in a slightly different way. This means I can't actually include it in the second visual so instead I have made it its own card. 

Does anyone know of a way to stack matrices such that they actually look like one?


Thanks, 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors