cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slounsbury Regular Visitor
Regular Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

Re: Dividing Columns and and filtering group to find total

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
Community Support Team
Community Support Team

Re: Dividing Columns and and filtering group to find total

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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors