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.

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.