Simple SumIfs in PBI

[FOXYBARK]

HI Team. 

I appreciate the help in advance. 

I would like to replicate a SUMIFS function in PBI. My sample data is shown below. In the example, my answer would be 6. I would like a new column to list the SUMIFS value for all rows in my real table. How can I achieve this in PBI? I am slightly familiar with the GROUP BY button in Power Query but I want to keep my original table in tact. 

Thanks, FB

[AnthonyJoseph · Accepted solution]

Hi @FOXYBARK , can you try this (calculated column):

sumif ex = 
CALCULATE(SUM('Table'[Count]),FILTER('Table','Table'[Group] = EARLIER('Table'[Group]) && 'Table'[Party] = EARLIER('Table'[Party])))

[v-rzhou-msft · Accepted solution]

Hi @FOXYBARK ,

Here I suggest you to create a measure as below.

How many from Group B,Party equal to Homecoming = 
CALCULATE (
    SUM ( 'Table'[Count] ),
    FILTER (
        'Table',
        'Table'[Group] = "B"
            && 'Table'[Party] = "Homecoming"
    )
)

Result is as below.

Best Regards,
Rico Zhou

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

[FOXYBARK]

I need an entire column, not a measure. I hit Solved by mistake. 

FB

[daXtreme]

[Your Column] = // calc column
// Don't use CALCULATE in calculated columns
// as this slows down calculations tremendously
// especially on big tables.
var vCurrentParty = T[Party]
var vCurrentGroup = T[Group]
var Output =
    sumx(
        filter(
            T,
            T[Party] = vCurrentParty
            &&
            T[Group] = vCurrentGroup
        ),
        T[Count]
    )
return
    Output

[AnthonyJoseph · Accepted solution]

Hi @FOXYBARK , can you try this (calculated column):

sumif ex = 
CALCULATE(SUM('Table'[Count]),FILTER('Table','Table'[Group] = EARLIER('Table'[Group]) && 'Table'[Party] = EARLIER('Table'[Party])))