Forum Discussion

Poornima2023's avatar
Poornima2023
Helper I
2 years ago
Solved

Formula to create win%

I need help in making measure where in I want to calculate Win%=Win/(Loss+Win)(The data contains pipeline where status is win, lost and active)

 

Please help

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Poornima2023 ,

    Please try to create a measure with below dax formula:

    Win% =
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Status] = "Won" )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), 'Table'[Status] IN { "Lost", "Won" } )
VAR _a =
    SUMX ( tmp, [Value] )
VAR _b =
    SUMX ( tmp1, [Value] )
RETURN
    FORMAT ( DIVIDE ( _a, _b ), "Percent" )

    For more details, please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • chonchar's avatar
    chonchar
    Helper V
    2 years ago

    Hi, 

     

    Try this:

     

    Win percentage = divide(sum(loss[loss table]),sum(win[win table]))

     

    That is not formatted properly, but that should work. 

     

    Best,

     

    Cam

    • chonchar's avatar
      chonchar
      Helper V
      2 years ago

      Notes on the divide function:

      -DAX has a safeguard for DIV/0

      -You must aggregate the total first, and then divide

      -There is a potential for a 3rd argument after the 2 parentheses, which is basically the end of an “IF” function in Excel, which means if you get null or 0, the last argument will return whatever you want it to be.

  • vinodDrinkPak's avatar
    vinodDrinkPak
    Helper II
    2 years ago

    Poornima2023 

     

    Win Percentage =
    DIVIDE(
    CALCULATE(
    COUNTROWS('Pipeline'),
    'Pipeline'[Status] = "Win"
    ),
    CALCULATE(
    COUNTROWS('Pipeline'),
    'Pipeline'[Status] = "Win" || 'Pipeline'[Status] = "Lost"
    ),
    0
    )

  • Poornima2023's avatar
    Poornima2023
    Helper I
    2 years ago

    There is single table in which status is marked as lost, win and active.

     

    The win% formula needs to be win%=won/(lost+won)

     

    Example of table is as below:

    Projetc Name

    		ValueStatus

    A

    		5Won

    B

    		10Won
    C100Lost
    D40Active

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Poornima2023 ,

    Please try to create a measure with below dax formula:

    Win% =
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Status] = "Won" )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), 'Table'[Status] IN { "Lost", "Won" } )
VAR _a =
    SUMX ( tmp, [Value] )
VAR _b =
    SUMX ( tmp1, [Value] )
RETURN
    FORMAT ( DIVIDE ( _a, _b ), "Percent" )

    For more details, please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.