Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Difference between power pivot in Excel and PBI desktop

  • 1.  Difference between power pivot in Excel and PBI desktop

    Bronze Contributor
    Posted Dec 04, 2017 10:06 AM
    Hi
    In Excel power pivot, the following formula does not sum:
    =Calculate(If([ClientsChangeOct] = 0, [ClientsDec],BLANK()))
    However, when I upload the file to PowerBI Desktop, it sums accurately. Does anyone else have a solution?
    Cheers
    Richard

    ------------------------------
    Richard Hanmer
    Owner
    OficinaEscutar
    Sao Paulo
    11982264474
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Difference between power pivot in Excel and PBI desktop

    Posted Dec 04, 2017 03:17 PM
    PowerPivot is probably running an older version of the Tabular Cube Engine as PowerBI is updated more often.

    However I'm not sure this would work in PowerBI and the syntax for this type expression should be the same.
    Is it a Calculated Column or Measure.

    I think either of these should product a sum that works. The Column will store a new value for each row and sum this and the measure will calculate it dynamically.

    Measure = CALCULATE(SUMX(Table,IF(Table[ClientsChangeOct] = 0, Table[ClientsDec], BLANK()) ))
    Column = IF(Table[ClientsChangeOct] = 0, Table[ClientsDec], BLANK())

    ------------------------------
    Phil A
    BI Architect
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Difference between power pivot in Excel and PBI desktop

    Bronze Contributor
    Posted Dec 05, 2017 06:33 AM
    Hi Phil
    #Excel
    #PowerBI
    Many thanks for your helpful reply. I am using measures rather than columns and the formulas you gave do not seem to work for me - probably I'm making a stupid error. I tried opening a column using the following formula:
    =Calculate(sumx(filter(Accounts1,Accounts1[SubCategory] ="Clients"),[Sum of Value]),'Calendar'[Month]="December"). But this did not work either throwing up a circular reference in another column whose formula is:
    =(sumx(Filter(Accounts1,Accounts1[SubCategory]="Expenses"),[Result]))
    The formula for 'Results' is:
    =CALCULATE([ResultAccum]-[ResultPriorMonth])
    and:
    ResultAccum = sumx(Filter(Accounts1,Accounts1[Type] = "CONTAS DE RESULTADO"), [Sum of Value])
    ResultPriorMonth is ResultAccum with DateAdd -1 Month
    There does not seem to be any circularity in any formula and the Expense column works fine until I try to add the new column.
    Any suggestions will be most welcome although the desktop app still works correctly
    Cheers and again many thanks​​
    Richard

    ------------------------------
    Richard Hanmer
    Owner
    OficinaEscutar
    Sao Paulo
    11982264474
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Difference between power pivot in Excel and PBI desktop

    Bronze Contributor
    Posted Dec 05, 2017 02:42 PM
    Edited by Domantas Gintauskas Dec 05, 2017 02:43 PM
    Maybe a stupid question, but where do you check the result of PowerPivot formula? Inside pivot table in Excel? Are there any relationships or only one table containing everything?


    ------------------------------
    Domantas Gintauskas
    R&D Data Analyst
    GN Resound
    52829987
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Difference between power pivot in Excel and PBI desktop

    Bronze Contributor
    Posted Dec 05, 2017 03:50 PM
    Hi Donats
    Not a stupid question at all!
    The individual balances are shown in both pivot tables (excel & pbi). I use sum on the columns in the excel power which gives me an answer off the pivot table. I then compare with the pbi automatic total and they agree.
    Very strange
    Cheers and thanks for replying 
    Richard

    Richard Hanmer
    +55 11 3071 4238
    +55 11 98226 4474
    Sent from my iPad



    Academy - Online Interactive Learning from Experts


  • 6.  RE: Difference between power pivot in Excel and PBI desktop

    Bronze Contributor
    Posted Dec 05, 2017 03:52 PM
    Sorry Domantas autocorrected your name which I did not spot!
    Apologies

    Richard Hanmer
    +55 11 3071 4238
    +55 11 98226 4474
    Sent from my iPad



    Academy - Online Interactive Learning from Experts