Forum Discussion

JMHenriques's avatar
JMHenriques
Frequent Visitor
2 years ago
Solved

SUM and/or count depending on value

Hi all,   This is driving me nuts... Take the following data as an example:   Contract Product Amount 1001 Product 1 1 1001 Product 1 1 1001 Product 1 0 1001 Product 1 0 ...
Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    Something like this might work for you...

    Measure = 
var _sum =
SUMX('Table', 'Table'[Amount])
var _zeroRows = 
COUNTROWS(FILTER('Table', 'Table'[Amount] = 0))
var _oneRows =
COUNTROWS(FILTER('Table', 'Table'[Amount] = 1))
var _rowCount =
COUNTROWS('Table')
Return
IF(
    _zeroRows + _oneRows = _rowCount,
    1,
    _sum
)

    Using your example table the above measure returns

     

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
2 years ago

Something like this might work for you...

Measure = 
var _sum =
SUMX('Table', 'Table'[Amount])
var _zeroRows = 
COUNTROWS(FILTER('Table', 'Table'[Amount] = 0))
var _oneRows =
COUNTROWS(FILTER('Table', 'Table'[Amount] = 1))
var _rowCount =
COUNTROWS('Table')
Return
IF(
    _zeroRows + _oneRows = _rowCount,
    1,
    _sum
)

Using your example table the above measure returns

 

  • JMHenriques's avatar
    JMHenriques
    Frequent Visitor
    2 years ago

    Many thanks jgeddes, it worked perfectly, well almost since I forgot a condition...   

    in the case of the contract having a number >1 even with zeros, than it needs to be that number. 

     

    In the case below, the number should be 3. 

     

    ContractProductAmount
    1001Product 13
    1001Product 13
    1001Product 10
    1001Product 10

     

    Added a new variable with a MAX function and it worked.

     

    NumberNE =
    var _sum =
    SUMX('Contracts per CaPM', 'Contracts per CaPM'[Product - Amount of contracted NEs per contract - Products])
    var _zeroRows =
    COUNTROWS(FILTER('Contracts per CaPM', 'Contracts per CaPM'[Product - Amount of contracted NEs per contract - Products] = 0))
    var _oneRows =
    COUNTROWS(FILTER('Contracts per CaPM', 'Contracts per CaPM'[Product - Amount of contracted NEs per contract - Products] = 1))
    var _plusRows =
    CALCULATE(MAX( 'Contracts per CaPM'[Product - Amount of contracted NEs per contract - Products]))
    var _rowCount =
    COUNTROWS('Contracts per CaPM')
    Return
    IF(
        _zeroRows + _oneRows = _rowCount,
        1,
        IF(
            _rowCount > 1,
            _plusRows,
           
        _sum)
    )