0

I'm very well versed in Excel, but very new to PowerPivot. I have a data set which includes game logs for every NBA player for every game of the season, and I want a new column which calculates the aggregate minutes played by a player's team on a given night.

In Excel this would be a simple SUMIF: [Minutes] if [Team] = [@Team] and [Date] = [@Date]

How would I approach this in PowerPivot, or is this something I would need to do before importing the data into PowerPivot?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Joe Schroeder
  • 15
  • 1
  • 3

3 Answers3

1

This sounds like something that would be better solved with a measure. See this link on measures vs calculated columns.

The typical consumption tool for a Power Pivot model is a pivot table, and only measures are re-evaluated for every cell in a pivot table. Calculated columns are only recalculated at model refresh time.

This would be as simple as the following:

TotalMinutes:=
SUM(FactGameLog[Minutes])

Then in your pivot table you can bring PlayerName, Team, and Date onto rows, columns, or filters (or slicers) as needed.

greggyb
  • 3,728
  • 1
  • 11
  • 32
0

My initial reaction is that this is a Pivot Table/Chart design question. You're pivot table queries the model for you. So you could have a simple SUM() measure, and then Slice by date, player and team on your row grouping. Optionally, you could add filters to look at a single player or team.

However, there is a DAX function for conditional summation, like a SUMIF/COUNTIF (also, conditional averages, min/max, count, and other aggregations).

You'll need to use a calculated measure. When you create the measure, you're going to want to use SUMX (also COUNTX, AVERAGEX, etc. for other aggregation types).

The big question is where are the values that you're comparing (@Team and @Date). Are they in another column, a related table, or are they static?

Anyhow, the sytanx will be something like this.

YourMeasure:=SUMX(TableName, expression)

Minute Total := SUMX ( GameData, IF ( [team] = "Team1" && [Date] = DATE ( 2015, 11, 5 ), [Minutes], BLANK () ) )

This example is just using a hardcoded string, but you can alter it depending on your need. Notice condition logic build into the second parameter. It will either aggregate the [minutes] field or have a blank value.

Hope this helps.

SQLTJ
  • 1
  • 1
  • Thanks for the response, I have since found 2 ways to handle this. One is to use a measure, and then use that in a pivot table. I was unaware that the result of a measure would get further filtered down within the pivot table. Also, I was able to use calculate(sum) with an "earlier" filter for a few of the things I needed. – Joe Schroeder Nov 06 '15 at 14:28
0

Hence you are dealing with conditional iterative sumif, in PowerPivot it is achieved via calculate+filter functions. for instance your function: [Minutes] if [Team] = [@Team] and [Date] = [@Date] => becomes in PP:

=Calculate(Sum(Table[Minutes]),Filter(Table, [Team] = Earlier([Team])),Filter(Table, [Date] = Earlier([Date])))

In Current scenario you will sum overall Minutes for each row that contains Team and Date.

ygaft
  • 314
  • 2
  • 8