cancel
Showing results for
Search instead for
Did you mean:
Helper II

## Dividing Columns and and filtering group to find total

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.

1 ACCEPTED SOLUTION
Community Support

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

1 REPLY 1
Community Support

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

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors