cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slounsbury
Helper II
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_nameYearTicketnbrBusGroupMonthly_InvoiceHour_ActualHours/INV
CompanyA2019101Managed Services8025160.4
CompanyA2019102Managed Security420069.360.06
CompanyA2019103Managed Services8025913.59
CompanyB2019104Managed Security9006015
CompanyB2019105Managed Services3000.2545
CompanyB2019106Managed Services9009010

 

This is what my end goal is:

CompanyA2019 Managed Services8026412.53
CompanyA2019 Managed Security420069.360.6
CompanyB2019 Managed Services9001506
CompanyB2019 Managed Security3000.2545

 

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

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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]

9.png

Best Regards

Maggie

View solution in original post

1 REPLY 1
v-juanli-msft
Community Support
Community Support

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]

9.png

Best Regards

Maggie

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors