Forum Discussion
Average from table with slicer from second table
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!
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
13 Replies
sergej_ogSuper User
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- 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? - sergej_og
this is what I get by using
AVG Item = AVERAGE(yout_table[Value_Item])
------------------------------------
Slicer works fine... - sergej_og
Hey Anonymous ,
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? - sergej_og
Ok, then check your datamodel. Something seems not correct.
My small model works fine with filter on item.K22:
M55: - sergej_og
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 - sergej_og
you are welcome.
Simple things are best things 🙂 Thanks sergej_og
The issue with this solution is that I think I get an average stock value per item.