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
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.