topic Re: Multiply Sum with Average in DAX Commands and Tips

Multiply Sum with Average

pbijess857 — Thu, 16 Dec 2021 00:28:14 GMT

Hello. I am trying to figure out how to get this to work in Power BI. The rows on my table represent tickets with a number value (hours of work.) This table is joined with a separate table that contains some additional information about the tickets from a different database, including the hours available for each team. I'm trying to sum up the hours of work associated with each ticket and divide by the time each team has available. I'm able to represent the sum of hours of work and the average hours available in a table, but riding the struggle bus trying to use them in additional calculations.

The underlying data would look something like this:

I think the next step is to get the data summarized by team which would ideally look something like this: (this is where I'm stuck)

And the end goal would be to sum up the average hours available by team (and sum of hours of work) to get a final utilization percentage.

Any help would be appreciated. I can't seem to find the right solution thus far.

Re: Multiply Sum with Average

VahidDM — Thu, 16 Dec 2021 00:53:22 GMT

Hi pbijess857

Try this code to add a new table:

Table 2 = VAR _A = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Group], 'Table'[Team], "SUM Hours of Works", SUM ( 'Table'[Hours of Work] ), "AVG Hours Available", AVERAGE ( 'Table'[Team Hours Available] ) ), "Sum Points / AVG Capacity", [SUM Hours of Works] / [AVG Hours Available] ) RETURN ADDCOLUMNS ( GROUPBY ( _A, [Group], "SUM Hours of Works", SUMX ( CURRENTGROUP (), [SUM Hours of Works] ), "SUM AVG Hours Available", SUMX ( CURRENTGROUP (), [AVG Hours Available] ) ), "Work/Available", [SUM Hours of Works] / [SUM AVG Hours Available] )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Multiply Sum with Average

pbijess857 — Thu, 16 Dec 2021 17:27:21 GMT

This looks like it has me back on the right track. Thank you for the help!