Sum of averages

[Veigar]

Hi to all,
I'm having some troubles...

I have 2 tables:
Costs(Cost_ID, Item, Cost, Year, Month)
Stock(Stock_ID, Item, Qty, Date)

"Item" in each table is linked to a third table ITEM(Item_id, Descr).

I want to calculate the sum of the average cost per item, and then I have to put it in a hystogram with this measure on y-axis and year-month on the x-axis.
(Date and year-month are already linked to the calendar)
I tried it in many ways and now i'm stuck...

[v-lionel-msft]

Hi @Veigar ,

Does your formula return the correct result? If not, please provide sample data.

Best regards,
Lionel Chen

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

[Veigar]

Hi @v-lionel-msft ,
In reality I don't know, because i have a very long dataset and i cannot do it by hand or in excel (that one because i'm good at it, but not that good).

Unfortunately i cannot give some data, i will try to mask them if you need them.

I want to calculate the value of a warehouse. I have these table:
ITEM[Itm_ID, Itm_descr]
COST[Cost_ID, Cost_Itm_ID, Cost, Year, Month] (This is the cost per item per year, per month)
STOCK[Stock_Itm_ID, Stock_Qty, Date]

Stock_itm_id and Cost_Itm_ID are linked to ITEM[Itm_ID], and the dates are all linked to a calendar.

I want to calculate this expression:
Average quantity for single item per month, multiplied by its month cost. So with this i will get the average value stored per month. Now i want to sum this value for every item in the warehouse. With this i will get the monthly value of the warehouse. My aim is to put this data in an hystogram and compare to the sum of sells.

[v-lionel-msft · Accepted solution]

Hi @Veigar ,

Or you can try this.

Average quantity for single item per month = 
AVERAGEX(
    FILTER(
        ALLSELECTED(STOCK),
        // [Month] is a calculated column in 'STOCK' column: MONTH([Date])
        [Stock_Itm_ID] = MAX(STOCK[Stock_Itm_ID]) &&  [Month]  = MAX(STOCK[Month]) 
    ),
    [Stock_Qty]
)

Average cost for single item per month = 
AVERAGEX(
    FILTER(
        ALLSELECTED(COST),
        [Cost_Itm_ID] = MAX(COST[Cost_Itm_ID]) && [Month] = MAX(COST[Month])
    ),
    [Cost]
) 

sum this value for every item = 
SUMX(
    FILTER(
        ALLSELECTED('ITEM'),
        [Itm_ID] = MAX('ITEM'[Itm_ID])
    ),
    [Average cost for single item per month] * [Average quantity for single item per month]
)

Best regards,
Lionel Chen

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

Or  

[Veigar]

Thanks for the answer first of all.

I made a mistake in the request, because i need the product between the average stock quantity and its averaged price for the month.

Now i tried with this:

sumx(values(ITEM[ITM_ID]), calculate(Average(Cost[cost]) * calculate(AVERAGE(stock[QTY])))).
Is it correct?

[amitchandak]

@Veigar , a new measure like

sumx(values(ITEM[Item_id]), calculate(Average(Costs[Cost])))