# Power BI Exchange

View Only

## 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
------------------------------

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

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

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

• #### 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
Richard

Richard Hanmer
+55 11 3071 4238
+55 11 98226 4474

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