cancel
Showing results for
Did you mean:
Helper I

## Conditional calculations

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

1 ACCEPTED SOLUTION
Community Support

Hi @nicoenz ,

``````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,

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

5 REPLIES 5
Helper I

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

Community Support

Hi @nicoenz ,

``````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,

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

Solution Sage

I forgot to put the result:

Solution Sage

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

Helper I

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.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors