none
Using INTERCEPT or other function that requires range parameters in a calculated field?

    Question

  • I have a dataset of the permon counter '% disk available' values per hour by server. This makes a very nice powerpivot chart. What I would like to do is filter on INTERCEPT() function. Passing the perfmon date/value range to INTERCEPT will tell me the date that '% disk availabale' value is predicted to be zero. I can do this easily in excel for a single server manually but i would like to add this INTERCEPT calculated filed to my powerpivot table so that i can filter on it and only show servers whose disk is predicted to run out within 30 days for example. I'm not sure if this is possible. Any help is much appreciated.
    Sunday, December 08, 2013 12:49 AM

Answers

  • It is possible, but you will have to basically simulate the entire formula in PowerPivot. Just to give an example

    Days from Start=[Date] - CALCULATE(min([Date]), ALLEXCEPT(Fact, Fact[Server]))
    
    Xavg=CALCULATE(AVERAGE([Disk Space]), ALLEXCEPT(Fact, Fact[Server]))
    
    Yavg=CALCULATE(AVERAGE([Days from Start]), ALLEXCEPT(Fact, Fact[Server]))
    
    X-Xavg=[Disk Space] - [Xavg]
    
    Y-Yavg=[Days from Start]- [Yavg]
    
    SlopeNum=([X-Xavg] * [Y-Yavg])
    
    SlopeDenom= [X-Xavg] * [X-Xavg]
    
    Slope=CALCULATE(sum([SlopeNum])/ sum([SlopeDenom]), ALLEXCEPT(Fact, Fact[Server]))
    
    Intercept=CALCULATE(min([Date]), ALLEXCEPT(Fact, Fact[Server])) + ([Yavg] - [Slope] * [Xavg])

    I have broken down the steps into separate calculated columns to that it is easy to understand. Hope this helps.


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Tuesday, December 17, 2013 5:35 PM
    Answerer

All replies

  • Anybody have an idea for 0x7?

    Thank you!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, December 16, 2013 6:45 PM
    Owner
  • It is possible, but you will have to basically simulate the entire formula in PowerPivot. Just to give an example

    Days from Start=[Date] - CALCULATE(min([Date]), ALLEXCEPT(Fact, Fact[Server]))
    
    Xavg=CALCULATE(AVERAGE([Disk Space]), ALLEXCEPT(Fact, Fact[Server]))
    
    Yavg=CALCULATE(AVERAGE([Days from Start]), ALLEXCEPT(Fact, Fact[Server]))
    
    X-Xavg=[Disk Space] - [Xavg]
    
    Y-Yavg=[Days from Start]- [Yavg]
    
    SlopeNum=([X-Xavg] * [Y-Yavg])
    
    SlopeDenom= [X-Xavg] * [X-Xavg]
    
    Slope=CALCULATE(sum([SlopeNum])/ sum([SlopeDenom]), ALLEXCEPT(Fact, Fact[Server]))
    
    Intercept=CALCULATE(min([Date]), ALLEXCEPT(Fact, Fact[Server])) + ([Yavg] - [Slope] * [Xavg])

    I have broken down the steps into separate calculated columns to that it is easy to understand. Hope this helps.


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Tuesday, December 17, 2013 5:35 PM
    Answerer