Divide 2 values on the same column?

[Vladracs]

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, example:

Customer	Variable_Value	Variance
A	2
A	4	200%
A	6	150%
A	3	-50%

Noting that if I filter to only 2 items say the first and last, I'd get the result

Customer	Variable_Value	Variance
A	2
A	3	150%

thx in advance.

Vladimir

[Padycosmos]

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]

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 😕

[Padycosmos]

Well. instead of index, you can try creating a Rank. These 3 videos can give you an idea:

https://www.youtube.com/watch?v=3IHsIngGdjU

https://www.youtube.com/watch?v=y2FFjfRD-Bo

https://www.youtube.com/watch?v=tS1ff6ouORg

[Vladracs]

Thanks for the hint on rankx, I can use it well to create ranks, but doesnt help me solve the original problem. It's crazy as this is a simple math dividing 2 rows...but in powerbi and dynamic tables, it seems like a lot of hassle 😕

[Vladracs]

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]

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)

[andhiii079845]

Do you have a "order by" column for the table? OFFSET need a order of the values.

[Vladracs]

I ended up changing the table to not contain the date and calculate with filter:

 Var em % =
VAR _MAX = maXX('Date','Date'[Ano])
VAR _MIN = MINX('Date','Date'[Ano])
RETURN
100*(CALCULATE([Sum Values],FILTER(ALLSELECTED(Ranking[Date]),YEAR(Ranking[Date])=_MAX))/ CALCULATE([TSum Values],FILTER(ALLSELECTED(Ranking[Date]),YEAR(Ranking[Date])=_MIN))).

It's not perfect as I had to add the meausure as a column, but I can hide and the end visual seems to be fine

[Vladracs]

Actually there is another column that determine the order...but I was trying to avoid showing it as it is a date (year), the table is filtered by date, and then I start getting these replies around using date formulas... and that doesnt do the job... see my previous post: https://community.powerbi.com/t5/Desktop/Find-growth-between-2-rows-after-filtering-a-table/td-p/311...