Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: SUM and MAX

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

adnanzakir

Helper II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2020
11:10 PM

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

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

az38

Super User VI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2020
11:17 PM

Hi @adnanzakir

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*

Highlighted

Rygaard

Helper II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2020
12:19 AM

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

az38

Super User VI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2020
11:17 PM

Hi @adnanzakir

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*

Highlighted

Rygaard

Helper II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2020
12:19 AM

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.

adnanzakir

Helper II

Re: SUM and MAX

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2020
03:16 AM

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

Highlighted
##

adnanzakir

Helper II

Re: SUM and MAX

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2020
03:18 AM

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

adnanzakir

Helper II

Re: SUM and MAX

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2020
10:20 PM

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

az38

Super User VI

Re: SUM and MAX

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2020
11:08 PM

Hi @adnanzakir

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*

Highlighted
##

adnanzakir

Helper II

Re: SUM and MAX

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-15-2020
02:07 PM

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

)))

Top Solution Authors

User | Count |
---|---|

409 | |

304 | |

84 | |

56 | |

49 |

Top Kudoed Authors

User | Count |
---|---|

541 | |

483 | |

168 | |

139 | |

112 |