Countif formula in PowerBi

[ledu]

Hi everyone,

I was hoping for some help with a formula in PowerBi similar to a countif.

I have a column that contains

• Detractors
• Promoters
• Passives
• Null

I need to calculate a new measures which is basically:

=(Promoters-Detractors)/(Detractors+Promoters+Passive)

How would I write this formula in PowerBi?

Thank you!

[ledu · Accepted solution]

Hi everyone!

Thank you all for your help. I realized that I was making this extra tricky and ended up using "quick measures" instead.

The formulas looked like this:

Detractors

NPS - Detractors =

CALCULATE(

    COUNTA('F_Form1'[Net Promoter Type]),

    'F_Form1'[Net Promoter Type] IN { "Detractors" }

)

Passives

NPS - Passives =

CALCULATE(

    COUNTA('F_Form1'[Net Promoter Type]),

    'F_Form1'[Net Promoter Type] IN { "Passives" }

)

Promoters

NPS - Promoters =

CALCULATE(

    COUNTA('F_Form1'[Net Promoter Type]),

    'F_Form1'[Net Promoter Type] IN { "Promoters" }

)

NPS Calculation

NPS Score = (([NPS - Promoters]-[NPS - Detractors])/([NPS - Promoters]+[NPS - Detractors]+[NPS - Passives]))*100

[v-eachen-msft]

Hi @ledu ,

You could also pivot the column to get sum.

Here is my test file with two workrounds.

[itsme]

CountIf =
VAR Detractors =
    CALCULATE (
        COUNT ( 'TableName'[ColumnName] ),
        FILTER ( 'TableName', 'TableName'[ColumnName] = "Detractors" )
    )
VAR Promotors =
    CALCULATE (
        COUNT ( 'TableName'[ColumnName] ),
        FILTER ( 'TableName', 'TableName'[ColumnName] = "Promotors" )
    )
VAR Passives =
    CALCULATE (
        COUNT ( 'TableName'[ColumnName] ),
        FILTER ( 'TableName', 'TableName'[ColumnName] = "Passives" )
    )
VAR Result =
    DIVIDE ( Promotors - Detractors, Detractors + Promotors + Passives )
RETURN
    Result

[Anonymous]

hi @ledu ,

Could you please provide an example of how your data is set up? It will be much easier to come up with a solution that way.

[mwegener]

Hi @ledu ,

Detractors = CALCULATE( SUM('Table'[Value]);FILTER('Table';'Table'[Column1] = "Detractors")) 

Promoters = CALCULATE( SUM('Table'[Value]);FILTER('Table';'Table'[Column1] = "Promoters")) 

Passives = CALCULATE( SUM('Table'[Value]);FILTER('Table';'Table'[Column1] = "Passives")) 

Measure = ([Promoters]-[Detractors])/([Detractors]+[Promoters]+[Passives])

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.