Asked by:
Line Space Calculated Fields or Measure?
General discussion

Hello,
I'm hoping someone will assist with creating a dynamic xaxis of equally spaced numbers in a PowerPivot model. The following function generates one of the most useful vectors I've ran across in my career;
Function LinSpace(ByVal a As Double, _ ByVal b As Double, ByVal num As Double, _ Optional ByVal endpoint As Boolean = True) As Double() 'Generate linearly spaced numbers.' Dim step As Double Dim i As Long Dim y() As Double ReDim y(0 To num  1, 0 To 1) If endpoint = True Then step = (b  a) / (num  1) 'with endpoint' ElseIf endpoint = False Then step = (b  a) / num 'without endpoint' End If For i = 0 To num  1 y(i, 0) = a + i * step Next 'debug.Print step' LineSpace = y End Function
Its functionality can be successfully recreated in a simple excel table as shown below;
However, in attempting the structure in PowerPivot, a calculated field fails to divide by the count (num), while the measure just returns an error. . .
This appears to be simple at first, but with more effort its becoming quite the challenge. Any suggestions would be most helpful. I can also provide the workbook if required.
Thank you!!!
Andrew S.
 Changed type Sturzaam Monday, May 4, 2015 2:16 PM Uploading Documents
Monday, May 4, 2015 12:43 AM
All replies

Hi Sturzaam,
Please do share the the workbook (assuming it contains nonsensitivities data). It will greatly help with developing and testing potential solutions.
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearnMonday, May 4, 2015 8:03 AM 
My apologies, clearly I do not use this forum often. Is it possible to upload documents to this discussion?
V/R, Andrew S.
Monday, May 4, 2015 2:09 PM 
Hi Andrew,
You can upload the file to some cloud storage like OneDrive or Dropbox and then provide the link here.
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearnMonday, May 4, 2015 2:52 PM 
Tuesday, May 5, 2015 6:13 AM

Thanks for providing the file. I'll be taking a quick look but encourage any other members of the forum to jump in if they can.
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearn Edited by Michael Amadi Tuesday, May 26, 2015 12:20 PM
Thursday, May 7, 2015 10:36 AM 
Hello again PowerPivot Community,
A dynamic calculated column of equally spaced values would be powerful for more sophisticated modeling. Does anyone have any ideas for this problem?
A model is provided here KDE
V/R, Andrew S.
 Edited by Sturzaam Monday, July 6, 2015 5:45 PM
Monday, July 6, 2015 5:37 PM 
Certainly doable  if a bit tricky.
You are going to have some "slope"... "The change in value per point". Ya?
Slope := DIVIDE(MAX(MyTable[MyValues])  MIN(MyTable[MyValues]), COUNTROWS(MyTable))
For any given point, ... it's value is going to be... MinValue + Slope * Point#
The tricky part is the Point#. Probably the easiest way to get that is via RANKX(). And I'm terrible at using that function :)
MyRank := RANKX(ALL(MyTable), CALCULATE(MAX(MyTable[ID]))
MyValue := MIN(MyTable[MyValues]) + [Slope] * [MyRank]
I actually think the MyRank is not QUITE right, but I'm hoping this gets you close enough to make the last leap...?
Monday, July 6, 2015 9:01 PM 
Thank you Scott,
The idea was very helpful. With minor tweaks the code below is in working order:
LinSpace:=MIN(MyTable[MyValues])+[Slope]*[Rank]
Slope:=DIVIDE(MAX(MyTable[MyValues])MIN(MyTable[MyValues]),
CALCULATE(COUNT(OtherTable[ID]),
ALL(OtherTable[ID])
)
)Rank:=RANKX(ALL(OtherTable[ID]),
CALCULATE(SUM([ID])),
,
1)1
V/R, Andrew S.
 Edited by Sturzaam Tuesday, July 7, 2015 4:29 AM
Tuesday, July 7, 2015 4:19 AM