cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Veigar
Frequent Visitor

Average warehouse value

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.

2 REPLIES 2
Ailsa-msft
Community Support
Community Support

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
Solution Sage
Solution Sage

// 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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors