Summings rows using different conditions in PowerBI

[jeroenvb1]

Hi all,

Recently, I have started using PowerBI to analyze data from our clients. Now, I have come to an problem for which i cannot find the solution. Below you find a simplification of data about two different projects, different project tasks and the amount of hours which it took to complete each task.
The thing i want to know is how much hours it took to complete task numbers 1000 up to 1999, 2000 up to 2999, etc. for each project. I think i need DAX formula to sum the data but until now i have not been able to accomplish this. The real data contains a lot different projects and tasknumbers up to 9999. I only need to know 1000-1999, 2000-2999, 3000-3999, .. etc.

I hope you guys could help me out.
Thank you in advance,

Jeroen

[TABLE="width: 230"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Projectnr.[/TD]
[TD]Projecttask[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]1004[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]1005[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]1010[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]1011[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]1012[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]2001[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]2002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]2003[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]2005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]1001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]1002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]1007[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]1008[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]1014[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]1015[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]2001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]2002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]2008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]84[/TD]
[/TR]
[TR]
[TD="align: right"]160010[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[Matt Allington]

There are various approaches. You could write one off measures.

=calculate(sum(table[hours]),table[projecttask]>=1000 && table[projecttask]<=1999)

or you could try banding. Alberto Ferrari : Banding with PowerPivot

 

[jeroenvb1]

There are various approaches. You could write one off measures.

=calculate(sum(table[hours]),table[projecttask]>=1000 && table[projecttask]<=1999)

or you could try banding. Alberto Ferrari : Banding with PowerPivot

Thank you for your reply, Matt.

I've found a solution which is similar to your =calculate function. I added another column in which I took only the first number of the projecttask column by using =left function. Then added a pivot table which sums all per projectnumber and new projecttask column.

The blog about banding was a little too difficult for me to understand, so i'll read it again later.

 

[Matt Allington]

Well good news. I decided (after trying to help you) to write my own banding blog post. It will be live on my website on Tuesday next week. http://xbi.com.au.