SUMIFS WITHOUT SOME IDS

[massotebernoull]

Hello everyone, I'm strugling to create a measure that gives me the sum of quantity that just includes IDs that doesn't have the collection.

In excel I have this table:

A	B	C
ID	MATERIAL	QUANTITY
A	COLLECTION	20
B	EXTRA	78
B	COLLECTION	23
C	EXTRA	9
C	COLLECTION	34
D	EXTRA	90
D	COLLECTION	3
E	EXTRA	11
F	EXTRA	21

I need to get the sum of quantity from IDs that just have the EXTRA material. In other words, just the ID E and F in this case.

In excel, I created other table just with E and F in column E, then applied SUM(SUMIFS(C:C;B:B;"EXTRA";A:A;E3:E4)).

Thanks for the help!

[tackytechtom · Accepted solution]

Hi @massotebernoull,

How about this:

Here the code:

TomsQuantityMeasure = 
VAR _helpTable =
SUMMARIZE ( 
    Table10,
    Table10[ID],
    "CountCollection", CALCULATE ( COUNT ( Table10[ID] ), Table10[MATERIAL] = "Collection" ),
    "CountExtra", CALCULATE ( COUNT ( Table10[ID] ), Table10[MATERIAL] = "Extra" )
)
RETURN
CALCULATE (
    SUM ( Table10[QUANTITY] ),
    FILTER ( _helpTable, [CountCollection] = BLANK() && [CountExtra] >= 1 )
)

Let me know if this helps! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[massotebernoull]

This one works pretty fine! Nice use of summarize, I never thought about it.

Thanks Tom!!

[Samarth_18]

Hi @massotebernoull ,

Create a measure like below:-

Measure = CALCULATE(SUM('Table'[QUANTITY]),FILTER('Table','Table'[MATERIAL] = "EXTRA"))

Thanks,

Samarth

[massotebernoull]

Thanks for your help Samarth, but your measure will count IDs that also have 'collection' in the Material and I must count just the IDs that doesn't count it