Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
// 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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |