Forum Discussion
SUMIFS Equivalent to Check for Row Value
Good evening.
I'm trying to build a SUMIFS equivalent formula that checks the value of a given row and sums the values in a column that match the values of that row. I wrote the following formula:
=CALCULATE(SUM('TABLE'[Quantity]),
FILTER('TABLE',[Registry]="2290"),
FILTER('TABLE',[Type]="Print")
)
I would really appreciate some help coding a version of it that checks the value of the current row rather than a fixed constant of "2290".
Thank you!
Hi GreenKnight1294 on this table my solution works.
I can't figure out what the problem is.
"Fix" a cell in POWER BI is not possible because of the logic it uses
Rather than working at the cell level, it works at the column level.
It's a game where you release filters and apply filters in a context.
Sumifs in the context of multiple conditions is like I showed calculate ([measure], condition A && condition B etc..)
Unfortunately, I don't know how to help beyond that...
pls try this
Column = VAR t1 = [Registry] VAR t2 = [Type] VAR _Results = SUMX(FILTER(ALL('Table'),'Table'[Registry]=t1&&'Table'[Type]=t2),[QTY]) RETURN _Results
10 Replies
- Ahmedx
Super User
pls try this
Column = VAR t1 = [Registry] VAR t2 = [Type] VAR _Results = SUMX(FILTER(ALL('Table'),'Table'[Registry]=t1&&'Table'[Type]=t2),[QTY]) RETURN _ResultsSir, you're a wizard. I wish I could give you more than just a thumbs up.
- Ritaf1983
Hi GreenKnight1294
Try :=CALCULATE(SUM('TABLE'[Quantity]),FILTER('TABLE',[Registry]="2290"&& [Type]="Print"),
)
Make sure that data type of registry column is text :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hey Rita,
Thank you so much for taking your time to check this.
This isn't exactly what I'm looking for.
Would it be possible to have these values calculated in every row of the table? Per your example I want the following to show in PowerPivot, so that the values that share the same filters will repeat and show the same sum.
Thank you.
- Ritaf1983
Hi GreenKnight1294
Unfortunately, I don't know how it works in power pivot.
In power bi to achieve your goal you can add a calculated column with Dax formula :test = if([Registry]="2290"&&'Table (2)'[Type]="print",CALCULATE(sum('Table (2)'[QTY]),ALLEXCEPT('Table (2)','Table (2)'[Registry],'Table (2)'[Type])),'Table (2)'[QTY]).I also updated a sample file .
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Thank you, Rita.
Here's a similar table to what I showed before with the desired result.
The fourth column has the following formula in Excel:
=SUMIFS($A$2:$A$20,$B$2:$B$20,B2,$C$2:$C$20,C2)
QTY Registry Type SUM 2 2290 print 40 8 2290 print 40 10 2290 print 40 12 2291 print 27 13 2291 print 27 2 2291 print 27 0 2291 print 27 3 2292 print 29 8 2292 print 29 7 2292 print 29 6 2292 print 29 5 2292 print 29 9 2290 print 40 11 2290 print 40 16 2294 print 43 19 2294 print 43 5 2294 print 43 3 2294 print 43 The most important part is that all the numbers that share the same registry value are added together.
