Conditional calculations

[nicoenz]

Hi,

I really need some help with DAX. I can do basic calculations but this one is exceeding my skills.

In the table below I need to calculate the Volume Increase effect that it is defined as follows 

For each product:

1. Agregate the quantity and sales for 2020 and 2021 (the table contains also 2018, 2019, etc but i'm only interested in 2020 and 2021)

2. If the quantity for any of those years = 0 exclude that product from further calculation.

3. If the agregated quantity 2021 > than that of 2020 then multiply the price by the difference in quantities (2021-2020). Price is defined as the "total sales / total quantity" (where total quantity <>0, of course) for 2020

4. If the agregated quantity 2021 < than that of 2021 then allocate a 0 for that product

In summary, for each product:

=if(or(a=0,b=0),0,if( b > a, (b-a)*k, 0))

where:

a: total quantity for that product in 2020
b: total quantity for that product in 2021
c: total sales for product in 2020
d: total sales for product in 2021

k: price for that product in 2020. Price is calculated as = c/a

Fiscal year	Product	QTY	NS	Price
2020	Product A	80	2062	25.78
2021	Product A	120	2941	24.51
2018	Product B	-6	-120	10
2019	Product B	-8	-160	20
2020	Product B	0	-195	0.00
2020	Product B	-64	-3953	61.77
2020	Product B	320	20475	63.98
2021	Product B	24	1829	76.21
2021	Product B	72	4798	66.64
2021	Product C	25	2855	114.20
2021	Product C	5	943	188.60
2021	Product C	0	0	0.00
2020	Product D	16	1921	120.06
2020	Product D	16	2782	173.88
2020	Product D	0	0	0.00
2020	Product D	0	0	0.00
2021	Product D	0	0	0.00
2021	Product D	0	0	0.00
2021	Product D	16	4278	267.38
2020	Product E	762	23416	30.73
2020	Product E	143	6137	42.92
2020	Product E	524	16395	31.29
2020	Product F	25	332	13.28
2020	Product F	0	0	0.00
2020	Product G	5	2365	473.00
2021	Product G	0	0	0.00
2021	Product G	25	2437	97.48
2020	Product H	181	2335	12.90
2021	Product H	0	0	0.00
2021	Product H	181	2452	13.55
2021	Product H	181	2299	12.70
2021	Product H	0	597	0.00
2020	Product J	111	30911	278.48
2020	Product J	0	0	0.00
2020	Product J	-16	-4224	264.00
2020	Product J	111	32923	296.60
2020	Product J	127	40644	320.03
2020	Product J	32	8321	260.03
2020	Product J	191	48554	254.21
2020	Product J	16	4475	279.69
2020	Product J	64	16886	263.84
2020	Product J	175	46570	266.11
2020	Product J	64	16831	262.98
2020	Product J	0	0	0.00
2021	Product J	0	0	0.00
2021	Product J	0	0	0.00
2021	Product J	0	0	0.00
2021	Product J	0	0	0.00
2021	Product J	127	42088	331.40
2021	Product J	127	41801	329.14
2021	Product J	111	35742	322.00
2021	Product J	127	39453	310.65
2021	Product J	95	25488	268.29
2021	Product J	159	42119	264.90
2021	Product J	64	16813	262.70
2021	Product J	95	25592	269.39
2021	Product J	64	17392	271.75
2021	Product J	79	21483	271.94
2021	Product J	32	8449	264.03
2021	Product J	48	12535	261.15
2020	Product K	0	332	0.00

I solved it in excel with a pivot table giving a "Volume Increase Effect" of  82767 USD

	Sum of QTY		Sum of NS		Price		Volume increase
	a	b	c	d	k
Row Labels	2020	2021	2020	2021	2020
Product A	80	120	2062	2941	25.775		1031
Product B	256	96	16327	6627	63.77734		0
Product C		30		3798	0
Product D	32	16	4703	4278	146.9688		0
Product E	1429		45948		32.15395
Product F	25		332		13.28
Product G	5	25	2365	2437	473		9460
Product H	181	362	2335	4751	12.90055		2335
Product I		0		597	0
Product J	875	1128	241891	328955	276.4469		69941.0549
Product K	0		332		0
							82767.05

I tried and tried and tried to do it in DAX and am certainly unsuccessful

[nicoenz]

Hi @v-yadongf-msft ,

thanks for your reply!

Your model works perfectly well in your pbix file

but when i do excatly the same in mine, it still returns a 0:

exactly the same as with @Bifinity_75 

Adding an index colum, did the trick!!

[v-yadongf-msft · Accepted solution]

Hi @nicoenz ,

Please add two additional measures to the measures @Bifinity_75  provided:

k = CALCULATE(sum('Table'[NS])/sum('Table'[QTY]),'Table'[Fiscal year]="2020")

Volume = 
var qty2020= CALCULATE(sum('Table'[Qty]),'Table'[Fiscal year]="2020")
var qty2021= CALCULATE(sum('Table'[Qty]),'Table'[Fiscal year]="2021")
return
IF(qty2021>qty2020,(qty2021-qty2020)*[k],0)

Sum_volumne = SUMX(ALL('Table'[Product]),[Volume])

Volume increase = IF(ISINSCOPE('Table'[Product]),[Volume],[Sum_volumne])

The result:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bifinity_75]

I forgot to put the result:

[Bifinity_75]

Hi @nicoenz , try this:

- Create this measure:

k = CALCULATE(sum('Table'[Ns])/sum('Table'[Qty]),'Table'[Year]="2020")

- Create this measure:

Volume increase = var qty2020=CALCULATE(sum('Table'[Qty]),'Table'[Year]="2020")
var qty2021=CALCULATE(sum('Table'[Qty]),'Table'[Year]="2021")
return
IF(qty2021>qty2020,(qty2021-qty2020)*[k],0)

Best regards

[nicoenz]

the result in your excel is correct. but the measure in powerbi returns a 0 because it is calculating the total qty for 2020 and 2021 and comparing both as an agregated total.

the measure should do the comparison at product level rather than total level.