Calculate average inventory value based on transaction?

Question

Hello everyone,

I am currently doing some prototyping with powerpivot and ERP-Systems. I try to create a powerpivot-calculation that shows me the average inventory on hand of different items.

The problem is that I am only able to retrieve the fact data on transaction levels such as: 

Item	Transaction Type	Quantity	Date	Location
815	Purchase	200	01.01.2012	A
815	Sales	10	05.01.2012	A
815	Sales	25	20.01.2012	C

I know that the average can be calculated differently based on the need of accuracy. Let's say I want to analyze the average per item on a daily base what would still allow me to aggregate the values for other periods.

So the result would look like: 

01.01.2012 - 04.02.2012: daily Average of 200

05.01.2012 -19.01.2012 : Average of 190 (200 minus sales of 10) etc. pp.

I could imagine running some ETL-transformation to populate a new table per item per day but I hope that I could avoid this.

I appreciate any input!

Regards,

jwissel

Answer 1

Hello jwissel,

Because this is a DE forum I am going to answer in German; if this is a problem for you, please give a brief reply.

Zunächst einmal, wenn Du abhängig vom TransactionType ein Vorzeichen für die Quantity setzen kannst (Purchase = +, Sale = -), solltest Du grundsätzlich die benötigten Werte berechnen können.

Mir ist aber Dein Datenmodel noch nicht klar, sind das kumulierte Werte je Tag/TransactionType oder handelt es sich um Einzelbewegungen?
Dann schriebst Du, Du möchtest die "Average Quantity" = Durchschnitt ermitteln, das ist ungewöhnlich. Normalerweise will man die Summe der Lagerbewegungen für eine z.B. Bedarfsplanung ermitteln. Wenn man 1000 Lagerbewegungen á 1 Stk hat, ist Avg = 1, bei 2 Bewegungen á 500 Stk, ist Avg = 500; in der Summe wurde aber die gleiche Menge bewegt.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Hallo Olaf,

danke schon einmal für Deine Antwort.

Es handelt sich um Einzeltransaktionen (ein Datensatz pro Ein- und Verkauf). In meinem Fall geht es darum, dass ich den durchschnittlichen Lagerbestand berechnen möchte. Ich möchte also z.B. die Frage beantworten können "wie viele Stück von Artikel 0815 hatte ich im Januar 2011 durchschnittlich auf Lager?".

Ich habe bisher nur die Idee, dies über eine eigene Tabelle zu lösen, die mittels einer ETL-Transaktion gefüllt wird. In dieser Tabelle würde ich dann für jeden Artikel pro Tag den Durchschnitt festhalten. Der Monatsdurchschnitt ließe sich dann leicht aus den Durchschnittswerten der Tage ermitteln. Schöne wäre es aber ja, wenn ich auf die Zwischentabelle verzichten könnte und mit Hilfe einer geschickten DAX-Formel in Powerpivot die Kalkulation hinbekäme.

Viele Grüße

jwissel

Answer 3

Ich hatte die Frage noch im richtigen Unterforum gestellt - dort befindet sich auch die Antwort

http://social.msdn.microsoft.com/Forums/de-DE/sqlkjpowerpivotforexcel/thread/7028096d-ad2f-4608-b688-8c2609d70dcc