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

[sudi_arta]

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]

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]

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]

Anyone Help?? Please.....

Thank's for theBardd and cnort21...