Powerpivot group sum on calculated field

[edusuro]

Hi,

I am using a calculated field to get sum of an item so that I can then manipulate it with some formulas. The rows on the pivot table are grouped into a category. I am trying to figure out a way to show the total sum of the group where the items belong instead of the sum for that single item. Example below:

Group 1 5

User 1 1
User 2 2
User 3 2
​

I need to figure out a formula that will provide the group total (in this example 5), no matter what row I'm at.

Thanks in advance!

 

[scottsen]

Having a bit of a tough time understand what you are asking, but I THINK you are saying that... you want to a measure that returns the Group total, even for individual users?

=CALCULATE([MyMeasure], ALL(MyTable[User]))

 

[edusuro]

Hi there scottsen!

You are correct, I want a measure that returns the SubGroup Total. I am currently using the following formula, however, it still returns the individual results.

=CALCULATE(SUM([MyMeasure]),ALL(MyTable[SubGroup]))

 

[scottsen]

Well, that is because you did ALL(MyTable[SubGroup]) instead of what I said... ALL(MyTable[User])

What ALL() does is remove the filter (on the specified column). So where normally each row is filtered by a specific user, you are saying "remove the filter on user" aka "show me the result pretending ALL() users are included".

 

[edusuro]

You were right. Thanks again for your help!

I had another question related to this. I have a calculated field showing up the value I want for each record. However, for the subtotal, I would like to show the sum of the values in each of the records instead of having the calculated field calculate the value again for the with the data at the aggregate level. How would I be able to obtain this result?

Thanks!

 

[scottsen]

The typical pattern for that is:

=SUMX(MyTable, [My Measure])