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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Weighted average Measure

Hi guys,

 

i need to create a weighted average measure for the following scenario:

 

I have 3 SKU on stock with a stock reach of 10 days each (Col: Stock reach per Qty). 

 

They all have 5 quantities on stock (Col: Average Qty on stock).

 

But they have different volume (Col: Volume per stock unit). 

 

Now i want to weight the total stock reach of the quantities with the volume. To illustrate i created the two Helping Columns where i calculate what i need for the weighted average. The result is the Col: Total Days Stock reach weighted. This column i want to create with a measure. But without the helping columns since i dont want to create them in the Power BI Model. Here the example:

 

     Helping Columns  
SKUStock reach per QtyAverage Qty on StockVolume per Stock Unit Total Days Stock reachTotal Volume for stock reachTotal Days Stock reach weightedCalculation
1231051 50500,45=50*50/5550
123410510 505004,50=50*500/5550
12345105100 50500045,05=50*5000/5550
TOTAL     5550  

 

How must i create the measure for this column?

 

Thanks guys!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous ,Try like

 

divide(sumx(all(Table),Table[Stock Unit]*Table[Stock reach]*Table[Stock reach]) ,sumx(all(Table),Table[Stock Unit]*Table[Stock reach]))

 

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

So 'Total Days Stock reach' and 'Total volumn for stock reach' are two calculate columns, right? Could you also share the dax expression of the two calculate columns?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous ,Try like

 

divide(sumx(all(Table),Table[Stock Unit]*Table[Stock reach]*Table[Stock reach]) ,sumx(all(Table),Table[Stock Unit]*Table[Stock reach]))

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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