Forum Discussion
Conditional sum in DAX
Hi ,
i have the the following table:
| Product | Fiscal year | Sum of QTY |
| Product A | 2021 | 0 |
| Product A | 2022 | 550 |
| Product B | 2021 | 2947 |
| Product B | 2022 | 2195 |
| Product C | 2021 | 1000 |
| Product C | 2022 | 0 |
| Product E | 2021 | 90 |
| Product E | 2022 | 0 |
| Product F | 2022 | 1800 |
| Product G | 2021 | 0 |
| Product G | 2022 | 0 |
I would like to know how much business we won and how much we lost in 2022. To do this the formulae will be:
Business won:
if( "volume of product 2021" = 0 and "volume of product 2022" > 0 return the sum of QTY for 2021)
Product A and F meet this condition => Business Won = 550 + 1180 = 1730
Business lost:
if( "volume of product 2022" = 0 and "volume of product 2021" > 0 return the sum of QTY for 2021)
Product C and E meet this condition => Business lost = 1000 + 90 = 1090
I tried many different options but can't solve it. I'd really appreciate a hand in this!!
For fun only, a showcase of powerful Excel worksheet formula,
5 Replies
For fun only, a showcase of powerful Excel worksheet formula,
Hi BeaBF,
With volume i meant "Sum of QTY". apologies for not being so clear.
Product Fiscal year Sum of QTY Product A 2021 0 Product A 2022 550 Product B 2021 2947 Product B 2022 2195 Product C 2021 1000 Product C 2022 0 Product E 2021 90 Product E 2022 0 Product F 2022 1800 Product G 2021 0 Product G 2022 0 Business won: I want a measure that will go through all the rows and check the QTY column. If in 2021 the QTY was 0 (or no value) and in 2022 QTY>0, then this means that it is business won for that product. I want to return the sum of all business won.
In the table, Product A has 0 in 2021 and 550 in 2022. Therefore it is business won of 550. Likewise, Product F's QTY is null for 2021 and 1800 for 2022. Therefore it is a business won of 1800
The total of business won is 2350.
I want a measure that will calculate that number
same logic for the Business lost
