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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KevinColes
Helper III
Helper III

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
mahoneypat
Employee
Employee

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! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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