Forum Discussion

Anonymous

Not applicable

6 years ago

win rate with filter

Hello PBI Community, I have the current win rate formula but would like to constrain it (via formula) to only those opportunities that are above $1,000,000. I can then compare it to our ...

Super User

6 years ago

Hi,

Share some data and show the expected result.

Anonymous
Not applicable
6 years ago
Ashish_Mathur

The formula below looks at ALL of the won opportunities versus the won/lost. I simply want the same basic result...but with a filter that only looks at those opportunities ("Description") that are $1,000,000 or above.

So, for example, our overall win rate might be 64%. But our win rate for deals over $1 million might only be 38%.

WinRate = DIVIDE (
    CALCULATE (
        COUNT ( 'AllOpps-Products'[Description] ),
        FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" )
    ),
    CALCULATE (
        COUNT ( 'AllOpps-Products'[Description] ),
        FILTER (
            ALL ( 'AllOpps-Products' ),
            'AllOpps-Products'[New Status] = "Won"
                || 'AllOpps-Products'[New Status] = "Lost"
        )
    )
)
- Ashish_Mathur
  Super User
  6 years ago
  Hi,
  
  Does this measure work?
  
  WinRate = DIVIDE (
  
      CALCULATE (
  
          COUNT ( 'AllOpps-Products'[Description] ),
  
          FILTER ( ALL ( 'AllOpps-Products' ), 'AllOpps-Products'[New Status] = "Won" && 'AllOpps-Products'[Description] >= 1000000)
  
      ),
  
      CALCULATE (
  
          COUNT ( 'AllOpps-Products'[Description] ),
  
          FILTER (
  
              ALL ( 'AllOpps-Products' ),
  
  ('AllOpps-Products'[New Status] = "Won"
  
                  || 'AllOpps-Products'[New Status] = "Lost") && 'AllOpps-Products'[Description] >= 1000000
  
          )
  
      )
  
  )