Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Calculation of two total values that are percentag...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

KevinColes

Helper I

Calculation of two total values that are percentages

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-07-2020
04:24 PM

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:

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

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

mahoneypat

Super User VI

Re: Calculation of two total values that are percentages

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-07-2020
04:59 PM

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

Proud to be a Super User!

Highlighted
##

Thanks,

Kevin

KevinColes

Helper I

Re: Calculation of two total values that are percentages

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-08-2020
08:25 AM

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

KevinColes

Helper I

Re: Calculation of two total values that are percentages

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-08-2020
09:10 AM

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,

Announcements

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

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

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

Top Solution Authors

User | Count |
---|---|

363 | |

118 | |

91 | |

89 | |

82 |

Top Kudoed Authors

User | Count |
---|---|

513 | |

190 | |

171 | |

154 | |

119 |