Forum Discussion

nicoenz's avatar
nicoenz
Helper III
3 years ago
Solved

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 follow...
Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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's avatar
Bifinity_75
Solution Sage
3 years ago

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's avatar
nicoenz
Helper III
3 years ago

 

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.