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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Justinnn
Regular Visitor

Weighted Average for several items over time

Hi all,

 

I'm struggling for a long time with applying a weighted average with three Tables for a specific item over time. I've seen other issues regarding weighted average, but not in this format.

 

Relationship Tables:

Justinnn_0-1676897586380.png

 

Table 1: Spend. Here the spend contains an identifier, date and price.

Justinnn_0-1676912688063.png

 

Table 2: Commodities; This table contains the raw commidities [items] that influence the price of our spend. The items are accompanied by a date + price.

Justinnn_1-1676912697279.png

 

Table 3: Weights. The weights have a weight [value], an Item and an Identifier.

Justinnn_2-1676912705456.png

 

Solution:
What I want is that, if I select an an identifier (say identifier = 1), that the filtered weights [Table 3, where Identifier =1] are multiplied by Table 1. Hence, 30% * 60, 30% * 63, 40% * 64. I've tried many different routes, including an inner join summation or a dynamic column based on a VAR, but they don't work. The final goal would be to link the weights again to an index, (which you can also find in the file), but I think I can manage the last part myself. Your help would be very much appreciated!

 

Link: Weighted Average of Indices.pbix

 

 

 

 

 

1 REPLY 1
amitchandak
Super User
Super User

@Justinnn , add a new column in table 2 using Table 2

 

New values = Table2[price] * maxx(filter(Table3, Table3[Item] = Table2[Item]) , Table3[Value])

 

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors