CODEWITHSUNDEEP
excelpowerpivotsumifs
Joe Schroeder
Joe Schroeder

Reputation: 15

SUMIF type function in PowerPivot

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?

Upvotes: 0

Views: 5978

Answers (3)

ygaft
ygaft

Reputation: 324

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.

Upvotes: 0

SQLTJ
SQLTJ

Reputation: 1

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.

Upvotes: 0

greggyb
greggyb

Reputation: 3798

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.

Upvotes: 1

Related Questions