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)
)