SUMIFS Equivalent to Check for Row Value

[GreenKnight1294]

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!

[Ritaf1983 · Accepted solution]

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...

[Ahmedx · Accepted solution]

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

[GreenKnight1294]

Sir, you're a wizard. I wish I could give you more than just a thumbs up.

[GreenKnight1294]

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.

[Ritaf1983]

Hi @GreenKnight1294 
Try :

=CALCULATE(SUM('TABLE'[Quantity]),FILTER('TABLE',[Registry]="2290"&& [Type]="Print"),

)

Make sure that data type of registry column is text :

Link to a sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[GreenKnight1294]

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

[GreenKnight1294]

Hey, Rita,

Sorry I couldn't answer sooner.

I tried out your solution and unfortunately this isn't the answer I'm looking for. Your formula is only adding the registries that share "2290" and "print" and I want a formula that can reference one of the columns in the table to get the value "2290" rather than a fixed value.

If the row has the value "2290" in the registry column I want it to add all the values that have 2290, but if another row has 2291 I want it to add all the values that have 2291 as well, and so on.

Do you know if this is possible using DAX?

Thank you so much for your time.

[Ritaf1983]

Hi @GreenKnight1294 .

I am sorry , i just not sure i understan what you have vs what you need.

Please share your table un iseful format ( not screenshot)  and desired result. I will try to help 

[GreenKnight1294]

Hey @Ritaf1983 

It seems I accidentally replied to the main post and not to you directly. Just wanted to let you know.

Thanks.

[Ritaf1983 · Accepted solution]

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...