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)


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

 

[theBardd]

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]

TheBardd, Thank's for your reply.

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

Please Help....

 

[theBardd]

Any chance you could post the workbook?

 

[sudi_arta]

Please refer to this link...

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

Thank's

 

[sudi_arta]

Hello theBardd,

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

Regards

 

[cnort21]

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]

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]

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]

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.