# Line Space Calculated Fields or Measure? • ### General discussion

• Hello,

I'm hoping someone will assist with creating a dynamic x-axis 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.

Monday, May 4, 2015 12:43 AM

### All replies

• Hi Sturzaam,

Please do share the the workbook (assuming it contains non-sensitivities data). It will greatly help with developing and testing potential solutions.

Regards,

Monday, 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,

Monday, May 4, 2015 2:52 PM
• Much appreciated,

V/R, Andrew S.

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,

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 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 Tuesday, July 7, 2015 4:29 AM
Tuesday, July 7, 2015 4:19 AM