Calculated field to show subtotal value

[bthomas]

I could be going about this all wrong and this may be why I am unable to find any information on this topic, but I am trying to create a calculated field that gives the subtotal value in a column. My ultimate goal is to get the percent of the subtotal value for each item.

I created a simple table named Grades (for learning Power Pivot). The table has the following fields.

Type
First
Last
Current Points
Total Points

I would like to create a column that totals the Current Points based on Type.

My current table looks like the example below with the calculation scenarios added. When I create the Current Points formula based on Type, it repeats the Current Points column. This is the formula I am using.

By Type:=CALCULATE([Total],Grades[Type]=Grades[Type])

Please let me know if you have any suggestions. Your help is greatly appreciated!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]First[/TD]
[TD]Last[/TD]
[TD]Current Points[/TD]
[TD]Total Points[/TD]
[TD]The calculation I would like.[/TD]
[TD]The calculation I am getting.[/TD]
[/TR]
[TR]
[TD]Female[/TD]
[TD]Shauna[/TD]
[TD]Thomas[/TD]
[TD]400[/TD]
[TD]1000[/TD]
[TD]650[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]Male[/TD]
[TD]Logan[/TD]
[TD]Thomas[/TD]
[TD]350[/TD]
[TD]1000[/TD]
[TD]550[/TD]
[TD]350[/TD]
[/TR]
[TR]
[TD]Male[/TD]
[TD]Jay[/TD]
[TD]Shaw[/TD]
[TD]200[/TD]
[TD]1000[/TD]
[TD]550[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Female[/TD]
[TD]Wendy[/TD]
[TD]Dem[/TD]
[TD]250[/TD]
[TD]1000[/TD]
[TD]650[/TD]
[TD]250[/TD]
[/TR]
</tbody>[/TABLE]

 

[Matt Allington]

Try this

=calculate([Total],all(tablename),values(tableName[type]))

read about values here https://powerpivotpro.com/2014/08/the-many-faces-of-values/

 

[zapppsr]

Create a Measure [TotalCurrentPoints] =SUM(Grades[Current Points])

Create a Column:

=CALCULATE(CALCULATE([TotalCurrentPoints];ALL(Grades);VALUES(Grades[Type])))

You need two calculate for context transition and values to filter for type. Also need all to get the whole table.

I hope it helps.

 

[zapppsr]

Create a Measure [TotalCurrentPoints] =SUM(Grades[Current Points])

Create a Column:

=CALCULATE(CALCULATE([TotalCurrentPoints];ALL(Grades);VALUES(Grades[Type])))

You need two calculate for context transition and values to filter for type. Also need all to get the whole table.

I hope it helps.

PS: Just change the ";" for ","

 

[bthomas]

Thank you all for your help!