Forum Discussion
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 |
| 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 🙂
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
2 Replies
jgeddesSuper User
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
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')ReturnIF(_zeroRows + _oneRows = _rowCount,1,IF(_rowCount > 1,_plusRows,_sum))
