SUM and/or count depending on value

[JMHenriques]

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
5555	Product 123	1
5555	Product 123	2
5555	Product 123	0
5555	Product 123	0
5555	Product 123	5
2020	Product 123	0
2020	Product 123	0

If for the same contract and Product I only have 0 and 1 value total should be 1 -> The case of contract 1001

If the contact and product have different numbers it should sum them -> in the case of contract 5555 it should be 8

If contract and product only have 0 it should count 1 -> In the case of contract 2020 it should return 1

I know I must get there with a measure but Im still search this beautiful forum for some wisdom 🙂 

[jgeddes · Accepted solution]

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]

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. 

Contract	Product	Amount
1001	Product 1	3
1001	Product 1	3
1001	Product 1	0
1001	Product 1	0

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)

)