cancel
Showing results for
Did you mean:
Highlighted 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 II

## Re: SUM and MAX

you can create a calculated table like

``````Table 2 =
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

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 II

## Re: SUM and MAX

you can create a calculated table like

``````Table 2 =
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

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 II

## 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

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 =
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]))
)))

Announcements #### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge! #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events. #### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community. Top Solution Authors
Top Kudoed Authors
Users online (1,665)