Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

VAR in formula

Hello, I am trying to construct a measure, using VAR.     Here is what I have so far.    Essentially, I am just trying to sum up the total count of Opportunities from our CRM, based on the statuses below.    But the measure returns an error at the RETURN step.   

 

Just not sure what is wrong with the formula, or perhaps there is a better way.  

 

Total Deals =
var _Open = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Open",
var _Inactive = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Inactive",
var _Lost = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Lost",
var _Won = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Won",
RESULT
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , Not clear on why you need 4 variable , looking at this it can be

    Total Deals =
    CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status] in {"Open","Inactive","Lost","Won"})

     

    or

    Total Deals =
    CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Open")
    + CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Inactive")
    + CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Lost")
    + CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Won")

     

    or

    Total Deals =
    var _Open = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Open")
    var _Inactive = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Inactive")
    var _Lost = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Lost")
    var _Won = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Won")
    return
    _Open+_Inactive+_Lost+_Won

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Try this

     

    Total Deals =
VAR _Open =
    CALCULATE (
        COUNT ( 'AllOpps-Products'[Description] ),
        'AllOpps-Products'[New Status] = "Open"
    )
VAR _Inactive =
    CALCULATE (
        COUNT ( 'AllOpps-Products'[Description] ),
        'AllOpps-Products'[New Status] = "Inactive"
    )
VAR _Lost =
    CALCULATE (
        COUNT ( 'AllOpps-Products'[Description] ),
        'AllOpps-Products'[New Status] = "Lost"
    )
VAR _Won =
    CALCULATE (
        COUNT ( 'AllOpps-Products'[Description] ),
        'AllOpps-Products'[New Status] = "Won"
    )
VAR _result = _Open + _Inactive + _Lost + _Won
RETURN
    _result

     

     

    Regards,

    Harsh Nathani

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Not clear on why you need 4 variable , looking at this it can be

    Total Deals =
    CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status] in {"Open","Inactive","Lost","Won"})

     

    or

    Total Deals =
    CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Open")
    + CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Inactive")
    + CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Lost")
    + CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Won")

     

    or

    Total Deals =
    var _Open = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Open")
    var _Inactive = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Inactive")
    var _Lost = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Lost")
    var _Won = CALCULATE(COUNT('AllOpps-Products'[Description]),'AllOpps-Products'[New Status]="Won")
    return
    _Open+_Inactive+_Lost+_Won

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    6 years ago
    You are missing the closing parenthesis of CALCULATE and you have extra "," at the end of each line and at the end you are returning something(RESULT) that is not defined within the VAR & RETURN scope.