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

## SUM and MAX

Hi Guys.

Requesting assistance on the following:

I have a table where each unique person ID has hours entered against a week.

 ID Weeks since placement Hours completed each week Target hours to be completed each week a 1 23 15 a 2 21 15 a 3 42 15 a 4 23 15 a 5 21 15 a 6 23 15 b 1 22 23 b 2 21 23 b 3 20 23 c 1 15 30 c 2 16 30

What I am trying to do is have a single record for an ID which shows the number of hours behind.

For example, for ID b, the person has completed 63 hours in total (22+21+20) where as the person should've done 69 according to target hours (23+23+23).

Is there a way I can have a single record which shows SUM(total hours completed) - (MAX(weeks since placement)*(Target hours to be completed))

The issue is, on the table it comes up as each row of unique of person with different weeks in each row. I wanted to summarize it in the format below

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User VI

## Re: SUM and MAX

you can create a calculated table like

``````Table 2 =
ADDCOLUMNS(
DISTINCT('Table'[ID]);
"Hours Behind";CALCULATE(SUM('Table'[Hours completed each week]);ALLEXCEPT('Table';'Table'[ID]))-CALCULATE(MAX('Table'[Weeks since placement]);ALLEXCEPT('Table';'Table'[ID]))*CALCULATE(MAX('Table'[Target hours to be completed each week]);ALLEXCEPT('Table';'Table'[ID]))
)``````

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Highlighted
Helper II

## Re: SUM and MAX

Untill you can write that yourself - I would ALWAYS reccomend having that in 3 or 4 sepperate rows Collumns, unless your data set already has more than Many rows and millions of lines. -

Also I would do a behind pr week collumn  - since this is also a good way to see a development if you plot it.

Also Since you have a Target pr week in each line  - why not use that  --- so if you ever encounter that it can warry over a project periond your calculations is still correct

Insert a new collumn with this formular if you just want the result:

``````Behind total = CALCULATE(SUM('Table'[Target hours to be completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID]))) -CALCULATE(SUM('Table'[Hours completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID]))) ​

New collumn Behind pr. week:``````

New collumn Behind pr. week:

``Behind this week = 'Table'[Target hours to be completed each week]-'Table'[Hours completed each week] ``

Hope this help... but also hope you add more columns to better understand the formulars and calculatitions, this has helped me alot of times.

7 REPLIES 7
Highlighted
Super User VI

## Re: SUM and MAX

you can create a calculated table like

``````Table 2 =
ADDCOLUMNS(
DISTINCT('Table'[ID]);
"Hours Behind";CALCULATE(SUM('Table'[Hours completed each week]);ALLEXCEPT('Table';'Table'[ID]))-CALCULATE(MAX('Table'[Weeks since placement]);ALLEXCEPT('Table';'Table'[ID]))*CALCULATE(MAX('Table'[Target hours to be completed each week]);ALLEXCEPT('Table';'Table'[ID]))
)``````

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Highlighted
Helper II

## Re: SUM and MAX

Untill you can write that yourself - I would ALWAYS reccomend having that in 3 or 4 sepperate rows Collumns, unless your data set already has more than Many rows and millions of lines. -

Also I would do a behind pr week collumn  - since this is also a good way to see a development if you plot it.

Also Since you have a Target pr week in each line  - why not use that  --- so if you ever encounter that it can warry over a project periond your calculations is still correct

Insert a new collumn with this formular if you just want the result:

``````Behind total = CALCULATE(SUM('Table'[Target hours to be completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID]))) -CALCULATE(SUM('Table'[Hours completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID]))) ​

New collumn Behind pr. week:``````

New collumn Behind pr. week:

``Behind this week = 'Table'[Target hours to be completed each week]-'Table'[Hours completed each week] ``

Hope this help... but also hope you add more columns to better understand the formulars and calculatitions, this has helped me alot of times.

Highlighted
Helper II

## Re: SUM and MAX

Thanks az38. I believe all that info is on a single calculated column?
Highlighted
Helper II

## Re: SUM and MAX

Thanks Rygaard. That's a good advice thank you for that. Having 3 or 4 columns would be easier to work with. I'll try to use the formula you provided. Cheers for that. Will let you know if it works.
Highlighted
Helper II

## Re: SUM and MAX

Hi AZ38. The solution worked to some extent, it gave me the following error:

"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Highlighted
Super User VI

## Re: SUM and MAX

It is a new calculated table (Modeling ribbon -> New table)

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Highlighted
Helper II

## Re: SUM and MAX

Thanks @az38

Sorry I am quite new to Power BI. I clicked on the ribbon to make a New table enterring your formula, and got the same error, This is my formula that I had been using:

Table 2 =
ADDCOLUMNS(
DISTINCT(vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]),
"Hours Behind", CALCULATE(SUM(vw_DES_Outcome_Tracking_13_26_52Weeks[Weekly Hours]), ALLEXCEPT(vw_DES_Outcome_Tracking_13_26_52Weeks,vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]) - CALCULATE(MAX(vw_DES_Outcome_Tracking_13_26_52Weeks[Anchor Week Diff]), ALLEXCEPT(vw_DES_Outcome_Tracking_13_26_52Weeks,vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]) * CALCULATE(MAX(vw_DES_Outcome_Tracking_13_26_52Weeks[BM Hours]), ALLEXCEPT(vw_DES_Outcome_Tracking_13_26_52Weeks,vw_DES_Outcome_Tracking_13_26_52Weeks[Jobseeker Id]))
)))

## Helpful resources

Announcements

#### Microsoft Ignite

This will be a conference that you do not want to miss!

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors