cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors