Forum Discussion
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, 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
9 Replies
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)RETURNDIVIDE(CALCULATE(sum('Table'[Variable_Value]),ALLSELECTED('Table'[Index]))-pval,pval)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- You may try thisPct change =VAR pval=LOOKUPVALUE('Table'[Variable_Value],'Table'[Index],SELECTEDVALUE('Table'[Index])-1)RETURNDIVIDE(CALCULATE(sum('Table'[Variable_Value]),ALLSELECTED('Table'[Index])),pval)
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 😕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
Do you have a "order by" column for the table? OFFSET need a order of the values.
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/3117372
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
