locked
Line Space Calculated Fields or Measure? RRS feed

  • 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;

    LinSpace_Table

    However, in attempting the structure in PowerPivot, a calculated field fails to divide by the count (num), while the measure just returns an error. . .

    PowerPivot_LinSpace

    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 non-sensitivities 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: @nimblelearn

    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,

    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

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

    Here is the Link: KDE.xlsx   


    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,

    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

    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