Average warehouse value

[Veigar]

Hi everyone, 
I have some tables:

ITEMS(ID, Description)
STOCK(Stock_ID, Stock_Item_ID, Stock_quantity, Stock_date)
COST(Cost_ID, Cost_Item_ID, Cost_Year, Cost_Month, Cost_Value)
Calendar(Date, month, Year),
Stock_Item_ID and Cost_Item_ID are linked to ITEMS(ID), and the dates are related.

The STOCK table has info about the quantity stored and the date (d/m/y).
The COST table has info about the Cost value ($) and the month and year.

I want to calculate the average value of the warehouse.
So, for every item in every day, i want to multiply the stored quantity for its monthly value. I want to sum these bvalue to get the value of the warehouse each day, and then do the average.

[v-yetao1-msft]

Hi @Veigar 

Because there are more related tables ,so could you provide some data or sample that can explain the needs you want ? It may help us to deal with the issue easier .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daxer-almighty]

// The assumption is that the fact table
// 'Stock' keeps track of all items on each
// day and even if there's no change in
// quantity of an item, still there's a
// row for this item on each day. If this
// is not the case, use Power Query to
// achieve this. If on a particular day
// there is no stock of a particular item,
// you don't have to put a row in there with
// quantity = 0. By doing the above your model
// will be bigger but your calculations will
// be much faster and your DAX will be easier.
// This trade-off is well worth it. Also, the Cost table
// is *NOT* connected to Calendar (they have
// different granularities). It is connected
// to Items, though. There are no bi-dir
// relationships in this model. Everything is just
// standard one-to-many from a dimension to a fact table.
// Fact tables must have all their columns
// hidden. Slicing can only be done via dimensions.


// Hidden auxiliary measure, not meant to be used
// on its own. It'll calculate the cost of the
// Warehouse on a particular day selected from
// Calendar. Need this to calculate the
// daily average value of the WH during ANY
// period of time.
[_DailyWHValue] = 
var OneDayVisible = SELECTEDVALUE( Calendar[Date] )
var YearOfVisibleDay = SELECTEDVALUE( Calendar[Year] )
var MonthOfVisibleDay = SELECTEDVALUE( Calendar[Month] )
var Total =
    SUMX(
        // Sum the value of all items visible
        // in the current context.
        SUMMARIZE( 
            Stock.
            Items[ID],
            Stock[Stock_Quantity]
        ),
        // Get the quantity and cost
        var Qty = Stock[Stock_Quantity]
        var CostPerItem =
            CALCULATE(
                SELECTEDVALUE( Cost[Cost_Value] ),
                Cost[Cost_Year] = YearOfVisibleDay,
                Cost[Cost_Month] = MonthOfVisibleDay
            )
        var TotalCost = Qty * CostPerItem
        return
            if( TotalCost, TotalCost )
    )
return
    Total
    

// The public measure
[Avg. Daily WH Value] =
AVERAGEX(
    DISTINCT( Calendar[Date] ),
    [_DailyWHValue]
)

// or

[Avg. Daily WH Value] =
AVERAGEX(
    DISTINCT( Calendar[Date] ),
    [_DailyWHValue] + 0
)

// This last measure will take into consideration
// also days when the total value of the WH was 0.
// The former one ignores days with a value of 0.