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
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 rightYes, 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?this is what I get by using
AVG Item = AVERAGE(yout_table[Value_Item])
------------------------------------
Slicer works fine...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. 🤔
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.
Ok, then check your datamodel. Something seems not correct.
My small model works fine with filter on item.K22:
M55: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.
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 modelWay simpler than I thought. Thank you so much for your help and pacience! That worked just fine!
you are welcome.
Simple things are best things 🙂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).