PowerPivot Equivalent of SUMIF()

[bauer24]

Hi all,

I'm trying to recreate a sumif() formula in PowerPivot but having no luck at all. Does anyone know the correct way to do this?

For example, I might have two columns. First column (Name) shows a pupil's name, the second column (Score) shows their test mark. I'd like to make a third column that shows the aggregate of their scores. This would be very easy using sumif() but I can't figure out how to do it using the calculate() of filter() functions.

Name,Score,NEW
Jack,10,14
Nina,11,19
Kim,1,5
Jack,4,14
Kim,4,5
Nina,8,19

 

[NickyvV]

What exactly is the reason you need the extra column for? Do you need it in a calculation afterwards?
If you just build a PivotTable it calculates it perfectly for you. Or you could try and define a measure?

 

[bauer24]

What exactly is the reason you need the extra column for? Do you need it in a calculation afterwards?
If you just build a PivotTable it calculates it perfectly for you. Or you could try and define a measure?

Hi Nicky,

I need it in the PowerPivot environment for use in another variable I'm creating later on. I'm trying to assign a value weighted on the percentage of scores.

So if the starting data was

David,20
David,30
David,10

I'd make this new metric that has the total for "David"

David,20,60
David,30,60
David,10,60

and then I can calculate the %

David,20,60,0.33
David,30,60,0.50
David,10,60,0.17

 

[JasonTomThomas]

You can just make a calculated column like
=calculate(Sum(Score[Score]), ALLEXCEPT(Score, Score[Name]))
Where Score is the table name

Cheers,

Jason

 

[bauer24]

Thanks mate, works perfectly.