Average from table with slicer from second table

[Basti76]

I have two slicers. Year and Week of year. I would now like to calculate a total average of stock value for the selected period.

I have two tables. 

1. Calendar table with individual dates, week of year and year. 
2. Table with stock values per item and per week. The date is first day of the week and connected to the calendar table.

My idea is that first in the background the total for each week is calculated and then divided by the number of weeks. How can I achieve that? 

Thanks!

[sergej_og]

you are welcome.
Simple things are best things 🙂

[sergej_og · Accepted solution]

Yes, I did´t considered this case so far...difficult ro cover your need without knowing your datamodel.
Easy to solve.
Divide 2 measures.
AVG = SUM(your_table[Amount Item]) / DISTINCTCOUNT(your_table[WeekNumber])

Try this approach.
Hope you can adjust the idea to your model

[Basti76]

Way simpler than I thought. Thank you so much for your help and pacience! That worked just fine!

[sergej_og]

Ok, then check your datamodel. Something seems not correct.

My small model works fine with filter on item.

K22:

M55:

[Basti76]

In the first screen the total is 83 for 26 weeks. So, shouldn´t it be 83/26 = 3.19? That would be the sum of the two. But yours shows 1.6. Which would be the average of the 2.0 and 1.2. 

[sergej_og]

Hey @Basti76 ,
pls show me what you mean by "But if I leave it open with all the outcome is incorrect".
Which other additional filter do you use?

[Basti76]

Sorry for the unclear explanation. If I would add a filter and only look at the average for one item I get the same result. If I don´t filter on item level (using a slicer) I don´t get the correct result.

[sergej_og]

this is what I get by using
AVG Item = AVERAGE(yout_table[Value_Item])

------------------------------------
Slicer works fine...

[Basti76]

Really much appreciate your help. 🙂 When I add another filter and look at this for just one item it works fine. But if I leave it open with all the outcome is incorrect. 🤔 

[Basti76]

I tried explaining it below the second point. I know it always make sense in my head but not for the rest. 🙂

This is what I get with the average formula. The correct value should be 31M divided by 26 weeks (around 1.2M).

[sergej_og]

Yes, I thought it was the target to calculate average over stock item. No?
What is your expected target value/visualization?
Maybe a Screenshot for that?

[Basti76]

Thanks @sergej_og 

The issue with this solution is that I think I get an average stock value per item. 

[sergej_og]

You can try to use the AVERAGE funktion.
Stock AVG = AVERAGE(yout_table[stock value]).
Put this measure on table visual and try to check the result by using your Calendar Table.

Makes sense?
Hope I catched your idea right