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)
  • Vladracs's avatar
    Vladracs
    Helper I
    3 years ago

    Hi I am trying to understand how can I create a index that is "dynamic" meaning it will change based on the slicer. 
    If I add a fixed item column , when I filter the formulas above dont reflect what I need as they will still show an entry that is not on the filtered (sliced) table at the final page ğŸ˜•