Forum Discussion

Vladracs's avatar
Vladracs
Helper I
3 years ago

Divide 2 values on the same column?

Hello Expert,   I am still trying to solve this problem, which I probably should have explained in a better way.   Is there a way to dynamicaly calculate the variance between 2 row in a column, e...
Need Help
Padycosmos's avatar
Padycosmos
Solution Sage
3 years ago

Your calculation is apparently incorrect, for example, (4-2)/2 = 100%
Please create an index column in Power query and use the following measure:

Pct change =
VAR pval=LOOKUPVALUE('Table'[Variable_Value],'Table'[Index],SELECTEDVALUE('Table'[Index])-1)
RETURN
DIVIDE(CALCULATE(sum('Table'[Variable_Value]),ALLSELECTED('Table'[Index]))-pval,pval)
 

 

Vladracs's avatar
Vladracs
Helper I
3 years ago

Hi, I'll give it a try it looks like it's going to work. 

Just the calculation is not what I want 🙂 I want to know the difference in percentage from one value to the next,  4 is 200% of 2,  it's just 4/2 

  • Padycosmos's avatar
    Padycosmos
    Solution Sage
    3 years ago
    You may try this
    Pct change =
    VAR pval=LOOKUPVALUE('Table'[Variable_Value],'Table'[Index],SELECTEDVALUE('Table'[Index])-1)
    RETURN
    DIVIDE(CALCULATE(sum('Table'[Variable_Value]),ALLSELECTED('Table'[Index])),pval)