powerbi

Replicate SUM with COUNTIF in Power BI

How can I replicate what I have in excel in Power BI table view. It would be best if I didn't have to make another column for the "Column with Y/N/NA" column and replace the letters with numbers. I tried an IF statement in Power BI that added a one to "column with numbers" if there was a Y in the "column with Y/N/NA" column but it didn't work. I am looking for an if statement that adds one to "columns with numbers" is there is a Y in the "Column with Y/N/NA" column or something like the excel formula that is below. I need to add another column that has these results for each row. enter image description here

Solution

  • If you just want to create a calculated column like Result, then

    Table1[Column with Numbers] + IF ( Table1[Column with Y/N/NA] = "Y", 1, 0 )

    If you want a measure that returns the sum of this column, here are a couple of options

    SUM ( Table1[Column with Numbers] )
    + CALCULATE ( COUNTROWS ( Table1 ), KEEPFILTERS ( Table1[Y/N/NA] = "Y" ) )

    SUM ( Table1[Column with Numbers] ) +
    + SUMX ( FILTER ( Table1, Table1[Y/N/NA] = "Y" ), 1 )