当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - Summing groups of rows together? DAX formula using PowerPivot?

问题描述:

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.

网友答案:

Can you please clarify why you need to do this via a DAX calculation? If you added a simple =SUM() calculation and then added Project to your PivotTable, then the context of each of those calculations would automatically result in the answers that you're looking for. Here's an example:

Sample Table

  • Table Columns
    • Project
    • Hours

SAMPLE DATA IN TABLE

Project 1,5
Project 1,5
Project 1,6
Project 2,5
Project 2,5
Project 1,3
Project 1,5

Calculated Field: TotalHours:=Sum([Hours])

PivotTable:

Values: TotalHours
Rows: Project (You could also choose a specific project as a filter)

Results:

Project 1: 24
Project 2: 10

In this example, because the context of each cell in which the TotalHours calculated field is used is limited to a single Project, you don't need to do anything special to group them to get the results you're hoping for.

Of course - this is all a moot point if you're trying to use these calculations as an intermediate step...

分享给朋友:
您可能感兴趣的文章:
随机阅读: