Forum Discussion
Substract first month value from rest all months
Hello Friends,
I am struggle to get logic. Month & Sales
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Dec |
| 21 | 25 | 35 | 45 | 65 | 69 | 70 | 80 | 87 | 100 |
My requirement is
A= 21(Jan)
B=100(Dec)
([Sales]-A)/(B-A)
The result should be
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Dec |
| 0 | (25-21)/(100-21)=0.**** | (35-21)/(100-21)=0.**** | 45 | 65 | 69 | 70 | 80 | 87 | 100 |
Kindly help me on solution
Anonymous if you have a table like this
month value jan 21 feb 25 march 35 apr 45 may 65 june 69 july 70 aug 80 sep 87 oct 90 nov 92 dec 100 and the final fraction to follow the following logic
A= 21(Jan)
B=100(Dec)
([Sales]-A)/(B-A)
You can reach what you need by using following measures
Sales:= SUMX(X,X[value]) only jan sales (A) := CALCULATE([Sales],ALL(X),X[month]="jan") only dec sales (B):= CALCULATE([Sales],ALL(X),X[month]="dec") Numerator (Sales-A):= [Sales]-[only jan sales (A)] Denominator (B-A):= [only dec sales (B)]-[only jan sales (A)] Fraction:= [Numerator (Sales-A)]/[Denominator (B-A)]
4 Replies
smpa01Community Champion
Anonymous if you have a table like this
month value jan 21 feb 25 march 35 apr 45 may 65 june 69 july 70 aug 80 sep 87 oct 90 nov 92 dec 100 and the final fraction to follow the following logic
A= 21(Jan)
B=100(Dec)
([Sales]-A)/(B-A)
You can reach what you need by using following measures
Sales:= SUMX(X,X[value]) only jan sales (A) := CALCULATE([Sales],ALL(X),X[month]="jan") only dec sales (B):= CALCULATE([Sales],ALL(X),X[month]="dec") Numerator (Sales-A):= [Sales]-[only jan sales (A)] Denominator (B-A):= [only dec sales (B)]-[only jan sales (A)] Fraction:= [Numerator (Sales-A)]/[Denominator (B-A)]Hi Anonymous ,
IF your data is really organized in the way it is shown (it seems unlikely), so a column for Jan, another column for Feb and so on,
Then you can just add columns to your table with the calculation as you described.
For instance for FEB : Calc Feb = Divide((sum(Data[Feb]) - Sum(Data[Jan])),sum(data[dec])-sum(Data[Jan]))Otherwise it is better to show how your data is organized, and also why Sep and Oct are missing.Jan