Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Basti76
Helper I
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
sergej_og
Super User
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.
Hope you can adjust the idea to your model

View solution in original post

13 REPLIES 13
sergej_og
Super User
Super User

you are welcome.
Simple things are best things 🙂

sergej_og
Super User
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.
Hope you can adjust the idea to your model

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

sergej_og
Super User
Super User

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

My small model works fine with filter on item.

sergej_og_0-1692347921475.png

K22:

sergej_og_1-1692348013895.png


M55:

sergej_og_2-1692348047904.png

 

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
Super User
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?

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
Super User
Super User

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

sergej_og_0-1692285266023.png
------------------------------------
Slicer works fine...

sergej_og_1-1692285325524.png

 

 

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

Basti76_0-1692283671157.png

 

sergej_og
Super User
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?

Basti76
Helper I
Helper I

Thanks @sergej_og 

 

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

sergej_og
Super User
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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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