# Could not add the field "TotalStock" to the PivotTable because the formula is invalid.



## sudi_arta (Apr 4, 2014)

*Please Help??*

I have a project to make the analysis of inventory levels that could be displayed in the days and months .


With PowerPivot , I make the measurement as follows :
1 . Unit CP (Cost Price) = lookupvalue ( InventoryLevelUnitPrice [ Cost Price ] , InventoryLevelUnitPrice [ Item No. ] , lastnonblank ( InventoryLevel [ Item No. ] , 1 ) ) / 1000
explanation - > Cost Price of each item


2 . Inventory Qty = calculate ( sum ( InventoryLevel [ Qty ] ) , filter ( InventoryLevel , InventoryLevel [ Physical Date ] < = Lastdate ( Date_Table [ Date ] ) ) )
explanation - > Inventory Qty in accordance with the dates shown


3 . Out avg = calculate ( sum ( InventoryLevel [ Qty ] ) , filter ( InventoryLevel , and ( InventoryLevel [ Trans Type ] = 6 , InventoryLevel [ Receipt Status ] = 1 ) ) , filter ( InventoryLevel , and ( InventoryLevel [ Physical Date ] > Lastdate ( Date_Table [ Date ] ) - 90 , InventoryLevel [ Physical Date ] < = Lastdate ( Date_Table [ Date ] ) ) ) ) / 90
explanation - > Qty average consumed within 3 months from the date shown


4 . Total Inv per item = InventoryLevel [ CP unit ] * InventoryLevel [ Qty Inv ]


5 . Total avg per item = InventoryLevel [ CP unit ] * InventoryLevel [ Avg Out ​​]


In this project I am using Table Disconnected Date_Table to determine the date that is displayed


*The problem that arises is when displaying measurement as follows :*
- TotalInv = sumx ( distinct ( InventoryLevel [ Item No. ] ) , InventoryLevel [ Inv Qty ] * InventoryLevel [ Unit CP ] ) or
explanation - > Total value of all inventory items in accordance with the dates shown


- TotalAvg = sumx ( distinct ( InventoryLevel [ Item No. ] ) , InventoryLevel [ Avg Out ​​] * InventoryLevel [ Unit CP ] )
explanation - > Total average value of all the items that are consumed in the last 3 months from the date shown


*
The error appears as below*

============================
Error Message:
============================


Exception from HRESULT: 0x800A03EC
----------------------------


----------------------------
*Could not add the field "TotalStock" to the PivotTable because the formula is invalid.*


============================
Call Stack:
============================




Server stack trace: 




Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.Office.Interop.Excel.PivotTable.AddDataField(Object Field, Object Caption, Object Function)
   at Microsoft.AnalysisServices.XLHost.Addin.ExcelInterOpUtil.AddToDataFields(ICalculatedMember calculatedMember, Int32 positionIndex, Boolean isSpecialColumnBasedNamedSetPresent)
----------------------------
   at Microsoft.AnalysisServices.XLHost.Addin.ExcelInterOpUtil.AddToDataFields(ICalculatedMember calculatedMember, Int32 positionIndex, Boolean isSpecialColumnBasedNamedSetPresent)
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.SetFieldOrientation(PivotArea location, IGeminiColumn column, Int32 positionIndex)
----------------------------
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.SetFieldOrientation(PivotArea location, IGeminiColumn column, Int32 positionIndex)
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.AddToFieldListInternal(PivotArea location, IGeminiColumn column, Int32 positionIndex)
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.MoveField(IGeminiColumn column, PivotArea source, PivotArea target, Int32 positionIndex)
   at Microsoft.AnalysisServices.XLHost.Addin.FieldListControl.MoveFieldInternal(IGeminiColumn field, PivotArea sourceArea, PivotArea targetArea, Int32 targetIndex)


============================


----------



## theBardd (Apr 6, 2014)

Shouldn't it be

=calculate ( 
    sum ( InventoryLevel [ Qty ] ) , 
	filter ( InventoryLevel , ( InventoryLevel [ Trans Type ] = 6 ) && ( InventoryLevel [ Receipt Status ] = 1 ) ) , 
	filter ( InventoryLevel , ( InventoryLevel [ Physical Date ] > Lastdate ( Date_Table [ Date ] ) - 90 ) && ( InventoryLevel [ Physical Date ] < = Lastdate ( Date_Table [ Date ] ) ) 
	) 
) / 90

i.e && not and


----------



## sudi_arta (Apr 8, 2014)

TheBardd, Thank's for your reply.

The problem still exist... for DAX measurement *TotalStock* (instead TotalInv) or/and *TotalAvg*.

Please Help....


----------



## theBardd (Apr 8, 2014)

Any chance you could post the workbook?


----------



## sudi_arta (Apr 10, 2014)

Please refer to this link...

https://drive.google.com/file/d/0BwfWtt8iUdQ3bFNCMFo2TmF3MVk/edit?usp=sharing

Thank's


----------



## sudi_arta (Apr 14, 2014)

Hello theBardd,

Please help or anyone ...... The link of file as above.... 

Regards


----------



## cnort21 (Apr 14, 2014)

On the "InventoryLevel" tab, are you hoping to see the same number across all items for both these equations?

For example: On 3/13/2014 - TotalInv = 14,115,455 & TotalAvg = 726,698 across every item?


----------



## theBardd (Apr 14, 2014)

What formula did you try for TotalInv. Did you join the date table to InventoryLevel, it isn't joined in the example workbook?


----------



## sudi_arta (Apr 14, 2014)

cnort21 : The existing value for grand total "Total Inv per Item" and "Total Avg Out per Item" are wrong.... So I need Dax formula to sum of the value "Total Inv per Item" and "Total Avg Out per Item", I was tried with DAX formula "TotalStock" and "TotalAvgOut" (exist on the PowerPivot field) but failed....

theBardd : Try to Insert Dax formula "TotalStock" and "TotalAvgOut" (exist on the PowerPivot field)... but failed....accordance with the above information, "In this project I am using Table Disconnected Date_Table to determine the date that is displayed"

Regards


----------



## theBardd (Apr 15, 2014)

Those formula in your post are incomplete, I wanted to see what you tried and how it actually failed.

I can see you are using a disconnected date table, but I cannot see why you don't just connect them as InventoryLevel has a date, then you get automatic filtering.


----------



## sudi_arta (Apr 4, 2014)

*Please Help??*

I have a project to make the analysis of inventory levels that could be displayed in the days and months .


With PowerPivot , I make the measurement as follows :
1 . Unit CP (Cost Price) = lookupvalue ( InventoryLevelUnitPrice [ Cost Price ] , InventoryLevelUnitPrice [ Item No. ] , lastnonblank ( InventoryLevel [ Item No. ] , 1 ) ) / 1000
explanation - > Cost Price of each item


2 . Inventory Qty = calculate ( sum ( InventoryLevel [ Qty ] ) , filter ( InventoryLevel , InventoryLevel [ Physical Date ] < = Lastdate ( Date_Table [ Date ] ) ) )
explanation - > Inventory Qty in accordance with the dates shown


3 . Out avg = calculate ( sum ( InventoryLevel [ Qty ] ) , filter ( InventoryLevel , and ( InventoryLevel [ Trans Type ] = 6 , InventoryLevel [ Receipt Status ] = 1 ) ) , filter ( InventoryLevel , and ( InventoryLevel [ Physical Date ] > Lastdate ( Date_Table [ Date ] ) - 90 , InventoryLevel [ Physical Date ] < = Lastdate ( Date_Table [ Date ] ) ) ) ) / 90
explanation - > Qty average consumed within 3 months from the date shown


4 . Total Inv per item = InventoryLevel [ CP unit ] * InventoryLevel [ Qty Inv ]


5 . Total avg per item = InventoryLevel [ CP unit ] * InventoryLevel [ Avg Out ​​]


In this project I am using Table Disconnected Date_Table to determine the date that is displayed


*The problem that arises is when displaying measurement as follows :*
- TotalInv = sumx ( distinct ( InventoryLevel [ Item No. ] ) , InventoryLevel [ Inv Qty ] * InventoryLevel [ Unit CP ] ) or
explanation - > Total value of all inventory items in accordance with the dates shown


- TotalAvg = sumx ( distinct ( InventoryLevel [ Item No. ] ) , InventoryLevel [ Avg Out ​​] * InventoryLevel [ Unit CP ] )
explanation - > Total average value of all the items that are consumed in the last 3 months from the date shown


*
The error appears as below*

============================
Error Message:
============================


Exception from HRESULT: 0x800A03EC
----------------------------


----------------------------
*Could not add the field "TotalStock" to the PivotTable because the formula is invalid.*


============================
Call Stack:
============================




Server stack trace: 




Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.Office.Interop.Excel.PivotTable.AddDataField(Object Field, Object Caption, Object Function)
   at Microsoft.AnalysisServices.XLHost.Addin.ExcelInterOpUtil.AddToDataFields(ICalculatedMember calculatedMember, Int32 positionIndex, Boolean isSpecialColumnBasedNamedSetPresent)
----------------------------
   at Microsoft.AnalysisServices.XLHost.Addin.ExcelInterOpUtil.AddToDataFields(ICalculatedMember calculatedMember, Int32 positionIndex, Boolean isSpecialColumnBasedNamedSetPresent)
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.SetFieldOrientation(PivotArea location, IGeminiColumn column, Int32 positionIndex)
----------------------------
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.SetFieldOrientation(PivotArea location, IGeminiColumn column, Int32 positionIndex)
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.AddToFieldListInternal(PivotArea location, IGeminiColumn column, Int32 positionIndex)
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.MoveField(IGeminiColumn column, PivotArea source, PivotArea target, Int32 positionIndex)
   at Microsoft.AnalysisServices.XLHost.Addin.FieldListControl.MoveFieldInternal(IGeminiColumn field, PivotArea sourceArea, PivotArea targetArea, Int32 targetIndex)


============================


----------



## cnort21 (Apr 15, 2014)

I added the below as calculated measures, which gives the sum for every item on each item row. Both numbers will be the same for every distinct item, but they will change based on the date.  

Grand Total for "Total Inv per Item"=calculate(InventoryLevel[Unit CP]*InventoryLevel[Qty Inv],all(InventoryLevel[Item No]))

Grand Total for "Total Avg Out per item" =calculate(InventoryLevel[Unit CP]*InventoryLevel[Avg Out],all(InventoryLevel[Item No]))


----------



## sudi_arta (Apr 20, 2014)

theBardd : Did you get the excel file? If already, try to insert the formula Dax "TotalStock" or "TotalAvgOut" on InventoryLevel sheet, it will display an error message as above. Regarding tables disconnected, I use it to cut the reporting period, so if I use linked tables, it can not (Please Correct Me if I am Wrong).

cnort21 : Thanks for the solution, but according to my information above that the value of the grand total for "Total Inv per Item" or "Total Avg Out per item" are wrong (eg. value 14,315,330 for "Total Inv per item"), the correct value should 69,621,276 for grand total of "Total Inv per item".


----------



## sudi_arta (Apr 27, 2014)

Anyone Help?? Please.....

Thank's for theBardd and cnort21...


----------

