DAX question: Summing groups of rows together? DAX formula using PowerPivot?

[robromo]

I am trying to sum up multiple rows in a PowerPivot model but have them grouped:

For instance if there are 20 total rows, Project A, B, C, D, E, F (5 projects - 4 entries per each project.

Is it possible to add up but group them?

So for instance: If: Project 1 5 hours Project 1 5 hours Project 1 6 Hours Project 1 3 hours Project 1 5 hours Project 2 5 hours Project 2 5 hours etc.

Is it possible to use DAX (Maybe Summarize?) to add them up but group it so the result would return as: Project 1 = 24 hours (which is all of them added up 5+5+6+3+5) and Project 2 = 10 hours (which is both project 2's added up, 5+5)
and so on and so forth?

I was looking at the =SUMMARIZE and followed the syntax but it would always give me an error/not work how I wanted it to.

EDIT: Trying to fix my formatting - How do I line break?​

 

[robromo]

I'm having a problem editing it. I want to edit in this table (to help visualize what I'm trying to accomplish).


This might make it easier to visualize:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Project A[/TD]
[TD]5 hrs[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]5 hrs[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]5 hrs[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]5 hrs[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]5 hrs[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]3 hrs[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]10 hrs[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]10 hrs[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]20 hrs[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]20 hrs[/TD]
[/TR]
</tbody>[/TABLE]


So if I wanted to find the totals for each:
Project A: 25 (5 + 5 + 5 + 5 + 5)
Project B: 23 (10 + 10 + 3)
Project C: 40 (20 + 20)

Is this possible to show? With a summarize?

But then as I'm showing it's not just 1 row, it's multiple rows, almost like a new table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Project A[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

So, unless I am totally not understanding, this will all magically "just work", almost without thinking Did you try not thinking?

Write a calculated field (measure): [Total Hours] := SUM(MyTable[Hours])

Then just go drop Project on Rows of you Pivot... and Total Hours on Values of your pivot, and <poof!>

 

[robromo]

So, unless I am totally not understanding, this will all magically "just work", almost without thinking Did you try not thinking?

Write a calculated field (measure): [Total Hours] := SUM(MyTable[Hours])

Then just go drop Project on Rows of you Pivot... and Total Hours on Values of your pivot, and

Thanks for the help!

So I'm following along with it, and I got the calculated field (measure down) - and as expected it shows all of hours summed up as 1.

So now going onto your next pt (which is assuming how it will break it up by each project, a, b and c) I don't follow you.

Drop the project on rows of your Pivot?

Are we talked in the powerpivot model? or a pivot table?

FYI, I am using PowerPivot and PowerView as my presentation not a pivot table or pivot chart.


to clarify:
what it is showing now is

the calculated measure: 200 hrs (just 1 row sums up all the hours as expected)

is it now possible for it to categorize it: into project 1: 70 Project 2: 30 project 3: 100 - so in essence it's summing it up, by showing how it's made up by the 3 categories to reach that sum total of 200.

 

[scottsen]

You just need to pull the project column onto your Power View canvas, no?

 

[robromo]

You just need to pull the project column onto your Power View canvas, no?

Unbelievable. That's as simple as it was. Worked perfectly!

I don't know how I made it so complex - you were right, all I had to was just drag the project column onto the new calculated measure in my PV report.


Thanks so much for the help I really appreciate it!

By the way - I can make a new thread to ask someone else - but since a new question came up now that I got this working:

Is it possible to get my 2 charts to work together flawlessly? For instance, right now with my 2 charts (1 has a heirarchy of 5 levels that can drill down 5 times - the other chart is this new one of sum of hours by each project).

So the drill downs work, and when I focus on one color (lets say blue) it does what it normally does and only highlights the blue (and grays out the other areas) and it does this on both charts - so they do communicate.

However, when I drill down on the one, it doesn't drill down on the other.. is this even possible?

 

[scottsen]

In my experience, when you want to veer off the path in Power View... the answer is always "sucks to be you". But I may be biased

I don't specifically know the answer, but I strongly suspect it's just not supported.

 

[Kazlik]

Robromo if you did need a calcualted colun you could use EARLIER to sum up each project name.


=CALCULATE(SUM(MyTable[Hours]), FILTER(MyTable,MyTable[ProjectName]=EARLIER(MyTable[ProjectName]))

 

[robromo]

In my experience, when you want to veer off the path in Power View... the answer is always "sucks to be you". But I may be biased

I don't specifically know the answer, but I strongly suspect it's just not supported.

Ok - that's what I'm figured. I'm moving on from that because I determined it's just not supported.

But anywho, I found a way to work around it.



Going back to my old point - which you solved for me! just making the total sum of hours as 1 calculated measure and then dragging that onto my PV report and then just adding Project type so it splits it by the 4 projects for total hours.

And that looks great and could be enough - but I want to take it one more step if possible...So I have that now in the matrix/table format (not a visual graph) and it looks as expected
[TABLE="width: 500"]
<tbody>[TR]
[TD]Project A[/TD]
[TD]12.5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]12.5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

But is it possible to add in a third column to calculate their percentages out of 100%?

So 12.5% 12.5% 25% and 50%.

I don't know if it's possible seeing as it would have to reference the number such as 12.5 (which is actually in my powerpivot model anywhere) just shows "Live" in the PowerView when I drag the project Type onto the 1 total sum of all of the project hours - does Power View store that anywhere? Does that make sense?

I was trying to just do a simple forumula: x/total hours * 100 to get the %, as anyone would do, but I don't know if it's possible to actually reference/point to that X (where it is the value of either 12.5,12.5, 25, or 50 which is the value of the total hours but only for that project) (i.e project a total/ all of the total hours *100).

I most likely made 0 sense there. Sorry for the wall of text.

 

[scottsen]

[Total Hours] := SUM(MyTable[Hours])
[Total Hours - All Projects] := CALCULATE([Total Hours], ALL(MyTable[Project Name]))
[% Hours] := DIVIDE([Total Hours], [Total Hours - All Projects])

You can set the formatting on your % measure... to display as a %. Then you should be dancing.