Conditional sum in DAX

[nicoenz]

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

[nicoenz]

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

[CNENFRNL · Accepted solution]

For fun only, a showcase of powerful Excel worksheet formula,

[nicoenz]

Hi CNENFRNL,

my table also contains data for other years which i would like to ignore. how can i filter them out?

Your DAX includes the data from those "unwanted" years

PREVIEW

[nicoenz]

exactly what i needed!!! thanks

[BeaBF]

@nicoenz  Hi!

What do you mean with "volume of product 2021" and "volume of product 2022"? Can you better explain the expected result, with a table maybe?

BBF