DAX Calculated Sum for Report graph using slicer

[TravisMM]

I am having a difficult time finding an answer to my needs online, so I was hoping someone in this community could be of help.

I have a raw data table that I will be using to build a report in Excel. I will be building a graph from my PowerPivot table, and using slicers to create a dynamic view of various business metrics.

Each row in my table will contain details for an individual for each month that is being reported on. I am trying to find a way to sum the values in a field for the month, but that total needs to be dynamic so that the slicer tool can be applied to the variables contained in other fields. A stripped down version of the table I will be using is below. Once I've got the syntax down I'll be able to run on my own.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Team[/TD]
[TD]Salesperson[/TD]
[TD]Assets[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]A[/TD]
[TD]John[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]B[/TD]
[TD]Sue[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]A[/TD]
[TD]Patty[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]B[/TD]
[TD]Raymond[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]2/1/2013[/TD]
[TD]A[/TD]
[TD]John[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]2/1/2013[/TD]
[TD]A[/TD]
[TD]Sue[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]2/1/2013[/TD]
[TD]B[/TD]
[TD]Patty[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]2/1/2013[/TD]
[TD]B[/TD]
[TD]Raymond[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]3/1/2013[/TD]
[TD]B[/TD]
[TD]John[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]3/1/2013[/TD]
[TD]A[/TD]
[TD]Sue[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]3/1/2013[/TD]
[TD]A[/TD]
[TD]Patty[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]3/1/2013[/TD]
[TD]B[/TD]
[TD]Raymond[/TD]
[TD]3000[/TD]
[/TR]
</tbody>[/TABLE]

As you can tell from the graph above the month will always be in the format MM/DD/YYYY.
The idea is that the additional column I am trying to create should show a sum of the assets for all salespeople for each individual month. I would like for this sum to change when a filter is applied. In the standard view the sum for 1/1/2013 would appear as 10,000. When I apply the slicer feature and narrow to team A it should show a sum of 3000 for 1/1/2013.

I'm new to DAX, and have been banging my head against the wall trying to figure this out. Any help you can provide would be GREATLY appreciated.

Thanks

 

[MD610]

It sounds like what you really want is a Measure (Excel 2010) or a Calculated Field (Excel 2013) and not a Calculated Column.

Calculated Columns are not dynamic. They are "hard coded" into the table when the column is created or the data is refreshed. The value will not change based on slicers or any other type of filtering. They work much like adding a column in a traditional excel table.

Measures are dynamic and will change based on pivot context and filters. Measures are what really set PowerPivot apart from traditional PivotTables.

Although they can become incredibly complex, the one you need is about as simple as it gets.

SumAssets: = SUM(TableName[Assets])

When you use this newly created measure called SumAssets in the Values section of your pivot, it will be dynamic and sliceable.

 

[TravisMM]

You are right, that using a measure will accomplish what I was looking to achieve. I was actually able to determine this EOD last Friday with the help of a coworker.

However... It turns out that I do need the function that I had asked for above, but need it to work without being dynamic.

Here is the basic jist of the situation, in more detail now that it is warranted:

I have three metrics I want to display. One is sales per salesperson per month, two is sales per "Other salesperson" per month (just think of them as different categories of salespeople), the third is "Total Sales by all salespeople per month"

I want to be able to apply the slicer and have the first variable change, but not the second or third. The first and second should not be a problem to accomplish. I have two columns I can use to create measures to produce the values I want to see. One is an individual monthly sales number for the 1st type of salesperson. The second column shows a monthly total of sales for the second salesperson for all rows in the column.
However, getting the third value to pull through without using a measure will be more difficult.

Any thoughts?

Thank you so much for your help so far here. Much appreciated!
____________________________________________________________________________

It sounds like what you really want is a Measure (Excel 2010) or a Calculated Field (Excel 2013) and not a Calculated Column.

Calculated Columns are not dynamic. They are "hard coded" into the table when the column is created or the data is refreshed. The value will not change based on slicers or any other type of filtering. They work much like adding a column in a traditional excel table.

Measures are dynamic and will change based on pivot context and filters. Measures are what really set PowerPivot apart from traditional PivotTables.

Although they can become incredibly complex, the one you need is about as simple as it gets.

SumAssets: = SUM(TableName[Assets])

When you use this newly created measure called SumAssets in the Values section of your pivot, it will be dynamic and sliceable.

 

[MD610]

I'm a little unclear on the description of your problem. Can you provide an example of your desired result?