Forum Discussion

FOXYBARK's avatar
FOXYBARK
Icon for Helper III rankHelper III
4 years ago
Solved

Simple SumIfs in PBI

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

 

DAX Calculation
  • AnthonyJoseph's avatar
    AnthonyJoseph
    4 years ago

    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])))

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago
    [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's avatar
    AnthonyJoseph
    Icon for Resolver III rankResolver III
    4 years ago

    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])))

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      FOXYBARK
      Icon for Helper III rankHelper III
      4 years ago

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

      FB