Dividing Columns and and filtering group to find total

[slounsbury]

I have kind of jerry rigged this table together in order to get every field that I need to get this metric(Monthly invoice amount divided by total hours). My problem is that I can divide these two columns and get the per ticket Hours/INV but when I use the visual level filters to combine the BusGroup for each company it just adds the Hours/INV together

For example, if I grouped by BusGroup I would get 173.99 instead of 12.53=(802/(5+59). The Monthly_Invoice is a static number, they are charged that no matter how many tickets they submit. 

My other problem is that some hours listed are below 1 so when it divides the column it gives a much bigger number than it should be. The real number can be obtained if multiplied.

company_name	Year	Ticketnbr	BusGroup	Monthly_Invoice	Hour_Actual	Hours/INV
CompanyA	2019	101	Managed Services	802	5	160.4
CompanyA	2019	102	Managed Security	4200	69.3	60.06
CompanyA	2019	103	Managed Services	802	59	13.59
CompanyB	2019	104	Managed Security	900	60	15
CompanyB	2019	105	Managed Services	300	0.25	45
CompanyB	2019	106	Managed Services	900	90	10

This is what my end goal is:

CompanyA	2019		Managed Services	802	64	12.53
CompanyA	2019		Managed Security	4200	69.3	60.6
CompanyB	2019		Managed Services	900	150	6
CompanyB	2019		Managed Security	300	0.25	45

I'm not really sure where to go form here, Any help or suggestions would be appreciated.

[v-juanli-msft · Accepted solution]

Hi @slounsbury

Create two measures

Measure = CALCULATE(SUM(Table1[Hour_Actual]),ALLEXCEPT(Table1,Table1[company_name],Table1[Year],Table1[BusGroup]))

Measure 2 = MAX([Monthly_Invoice])/[Measure]

Best Regards

Maggie