DAX to sum specific rows in a column

[CosmicHorse95]

I have a table with [State], [#Samples], [#Positive], and [#Negative] Columns.

I'm trying to create a DAX that will add up all of the [#Negative] for each state.

In other words, I basically need AlabamaNegSamples=[#Negative] if [State]=Alabama

But I'm not sure how to write that in DAX language.

Thank you in advance!

[Greg_Deckler]

@CosmicHorse95 Try:

Measure = 
  SUMX(SUMMARIZE('Table',[State],"__Neg",SUM([#Negative])),[__Neg])

[CosmicHorse95]

@Greg_Deckler I got the following error message (see picture)

Did you mean to list "__Neg" or was I supposed to change that to "#Neg" as that's the name of the column?

[Greg_Deckler]

@CosmicHorse95 Try changing your Data Type for that column to Whole number or:

Measure = 
  SUMX(SUMMARIZE('Table',[State],"__Neg",SUM(VALUE[#Negative]))),[__Neg]

[CosmicHorse95]

@Greg_Deckler 

Ok so changing the Data Type to a whole number helped, and the DAX is "valid" but the calculation is wrong. It should only be 15, but it calculated 89...

89 is the grand total for all negative samples for all states, but I'm trying to have a Measurement for the Total of Negative Samples for Each state.

[Greg_Deckler]

@CosmicHorse95 So normally you would just put State in a table visual or other visual and then use a simple SUM of [#Negative] column for that.

[CosmicHorse95]

@Greg_Deckler 

I'm sorry, I don't think I understand. I got this far, but I don't need the grand total, as I already have that. I'm trying to explain what I need a different way, I'm sorry if I'm not being clear enough.

Alabama has 2 negative samples. I need a measure that will give me the total of just the Alabama negative samples. When I use the SUM function, it sums the entire column, and i do not want that.

[Greg_Deckler · Accepted solution]

@CosmicHorse95 

Measure = SUMX(FILTER('Table',[State]="Alabama"),VALUE([#Negative]))

[CosmicHorse95]

It works! Thank you so so much!!!!