Forum Discussion

ledu's avatar
ledu
Regular Visitor
6 years ago
Solved

Countif formula in PowerBi

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!

Need Help
  • ledu's avatar
    ledu
    6 years ago

    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
     

5 Replies

  • itsme's avatar
    itsme
    Resolver I
    6 years ago

    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

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

  • ledu's avatar
    ledu
    Regular Visitor
    6 years ago

    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