PowerBI COUNTIFS Function in Excel

[Anonymous]

Hi,

I'm trying to recreate the Excel formula below in PowerBI and hoping someone can help.

=COUNTIFS(G22:G312, G22, N22:N312,">"&N22)

I'm essentially looking to search for the value in column G (which basically looks for all the members within a certain group), and then count the number of those rows that have a value in column N greater than the value in column N for the cell referenced.

I've tried certain combinations using =COUNTROWS(FILTER(

but am unable to find an exact match.

Thank you in advance!

[Ashish_Mathur]

Hi,

Try this calculated column formula

=calculate(countrows(Data),filter(Data,Data[ColumnG]=earlier(Data[ColumnG])&&Data[ColumnN]>earlier(Data[ColumnN])))

Hope this helps.

[Anonymous]

=calculate(countrows(Data),filter(Data,Data[ColumnG]=earlier(Data[ColumnG])&&Data[ColumnN]>earlier(Data[ColumnN])))

Need to delete the additional "&" symbol from the formula.

[ArmandoFranco · Accepted solution]

I guess you are trying to create a calculated column.

Your formula would be easier to translate if you were using tables in Excel. This is how your formula would look with tables:

=COUNTIFS([ColumnG], [@ColumnG], [ColumnN],">"&[@ColumnN])

So, let's assume you have a table called Table and it has ColumnA, ColumnB, ...

=
VAR __CurrentG = 'Table'[ColumnG]
VAR __CurrentN = 'Table'[ColumnN]
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            AND ( 'Table'[ColumnG] = __CurrentG, 'Table'[ColumnN] > __CurrentN )
        )
    )

I'll try to explain and if someone detects a mistake, please do correct me.
You have a row context where you see only one row at a time. That is your scope.
You don't have a filter context at this step.
By using CALCULATE, you create a new inner row context, and in order to pass values from the outer row context to the inner one, you define VAR __CurrentG and VAR __CurrentN.
In the inner row context, you use FILTER to keep only the rows that match your VARs.
And then COUNTROWS counts how many rows there are in the inner row context for each row in the outer row context.

I'd recommend watching YouTube videos from Excel Is Fun and SQLBI on those topics.

[Anonymous]

Nevermind, this worked perfectly! Thank you!

[Anonymous]

Thank you for the response! I definitely think it's on the right track. However, Column G is in one table (main table) and column N is in another table (all measures). Would this formula still work?