locked
PowerPivot - Calculate Function doesn't work? RRS feed

  • Question

  • Hey everyone,

    i really hope someone can help me out with this. I am trying to repeat the sample of Marco Russo and Alberto Ferrari in their PowerPivot-book (which is great, btw). I understand the calculate function, but the function doesn't return what it is supposed to return. In order that you know what I am talking about, it's this function:

    CALCULATE(SUMX(Orders,Orders[Amount]),All(Orders[Channel]))

    The function just doesn't ignore the channel-filter - and I have no idea why.

    I hope someone can help me! I'm getting desperate!

     

    Erschone

    Friday, July 1, 2011 5:46 AM

Answers

  • I think this is the issue.  The "CH06-01-EvaluationContext-demo.xlsx" companion file uses Channels[Channel] in the slicer, but page 179 on the book tries to clear that filter using ALL ( Orders[Channel] ).   I tested changing it to ALL ( Channels[Channel] ) and it works as expected now.


    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    • Marked as answer by Challen Fu Thursday, July 7, 2011 10:09 AM
    Wednesday, July 6, 2011 2:00 AM
    Answerer

All replies

  • Erschone,

    I have tried right now (it seemed pretty strange that such a fundamental function did not work) and... well, the CALCULATE function works as expected. :)

    Your formula, in the examples of our book (I tried with 06-01) returns 749 even if the filter on Channel="Store" returns 157 for the SUM of the amount.


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    Friday, July 1, 2011 11:47 AM
  • Dear Alberto,

     

    that's what I thought! I swear to God that I tried that example of your book 100 times - but it just doesn't return 749. I uploaded the file to skydrive (https://skydrive.live.com/redir.aspx?cid=83a73e2364435b17&resid=83A73E2364435B17!152). As you will see - both measures, Sum of Amount and AllChannels deliver the same result. I would be awesome you may help me!

     

    Erschone

    Friday, July 1, 2011 3:42 PM
  • Erschone,

    Don't worry, you don't need to swear. :) That said, the issue is becoming interesting. I have downloaded the workbook and... yes, it is not working as expected, the ALL seems not to be working inside CALCULATE. I am going to investigate it a bit more... stay tuned, hopefully we'll find the reason for this funny behavior.

     


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    Friday, July 1, 2011 4:09 PM
  • Thanks Alberto for your support! I just thought I was going crazy! I will stay tuned! Thanks and I wish you a good weekend!

     

    Erschone

    Friday, July 1, 2011 7:24 PM
  • I think that the problem is that the right formula should be

    =CALCULATE( SUMX (Orders, Orders[Amount]), ALL(Channels[Channel]) )

    You put ALL(Orders[Channel]) instead of ALL( Channels[Channel] ) - if you copied the formula from the book, can you specify me what is the page where you copied it from?

    Thanks,

    Marco Russo
    http://www.powerpivotworkshop.com
    http://sqlblog.com/blogs/marco_russo


    Marco Russo
    Saturday, July 2, 2011 11:34 AM
  • Hello Marco,

    thanks that you are trying to help as well. Yes, I copied the formula from the book, it is on page 179 and 180. I will try your suggestion!

     

    Thanks!

    Erschone

    Saturday, July 2, 2011 8:25 PM
  • I think this is the issue.  The "CH06-01-EvaluationContext-demo.xlsx" companion file uses Channels[Channel] in the slicer, but page 179 on the book tries to clear that filter using ALL ( Orders[Channel] ).   I tested changing it to ALL ( Channels[Channel] ) and it works as expected now.


    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    • Marked as answer by Challen Fu Thursday, July 7, 2011 10:09 AM
    Wednesday, July 6, 2011 2:00 AM
    Answerer
  • I checked the book and the companion files.

    The workbook CH06-01-EvaluationContext-demo.xlsx is the final result after several modification. We should have included the starting version, containing just one table (Orders) and then the final version (the current one, which includes Channels and Cities tables).

    If you look at Figure 6-12, you can see that in this case we still have just one table in the model (just Orders). Thus, the slicers are based on columns of the Orders table and not on the other two tables, that we include only starting from page 181 (Figure 6-14 and following).

    I understand we should improve this in the next release.

    Thanks!

    Marco Russo
    http://www.powerpivotworkshop.com
    http://sqlblog.com/blogs/marco_russo


    Marco Russo http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo
    Tuesday, July 12, 2011 8:58 PM