cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## 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!

1 ACCEPTED SOLUTION
Super User

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.

13 REPLIES 13
Super User

you are welcome.
Simple things are best things 🙂

Super User

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.

Helper I

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

Super User

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

My small model works fine with filter on item.

K22:

M55:

Helper I

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.

Super User

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?

Helper I

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.

Super User

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

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

Helper I

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. 🤔

Helper I

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).

Super User

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?

Helper I

Thanks @sergej_og

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

Super 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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors