Weighted average from other table

[Anonymous]

Hello guys, I need your help on my task here...

I have two tables as shown below:

I have 3 things to achieve:
1. Create columns like on a cell that I highlighted with a red-dotted box in Table 1. note: value on G column is acquired from table 2.

2. Create a measure like value on a cell with a blue-dotted box. The end goal is to create historical using line graph for that value by project.

3. Create a measure like value on a cell with a yellow-dotted box. The end goal is to create historical using line graph for that value by project.

the sample data: DataSample.xlsx - Google Drive 

Really appreciated for any helpful suggestions or step by step explanation. Thank you

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can create four measures to meet your requirement.

1. Create a relationship between sheet 1 and sheet 2.

2. We can create two measures to get the sheet 2 result.

Take up = DIVIDE(SUM('Sheet 2'[Sold]),SUM('Sheet 2'[Total Unit]))

Percentage Remaining Unit = 
DIVIDE(
    SUM('Sheet 2'[Remaining]),
    CALCULATE(SUM('Sheet 2'[Remaining]),ALLSELECTED('Sheet 2')))

3. Then we can create two measures to get the sheet 1 result.

Average Unit Price per Type = 
var _average = 
DIVIDE(
    CALCULATE(SUM('Sheet 1'[Price/Sqm]),FILTER(ALLSELECTED('Sheet 1'),'Sheet 1'[Unit Type]=MAX('Sheet 1'[Unit Type]))),
    CALCULATE(DISTINCTCOUNT('Sheet 1'[Floor Area]),FILTER(ALLSELECTED('Sheet 1'),'Sheet 1'[Unit Type]=MAX('Sheet 1'[Unit Type]))))
return
_average

Measure = SUMX('Sheet 2',[Average Unit Price per Type]*[Percentage Remaining Unit])

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Anonymous]

it is totally fine, i just give it a little adjustment and your solution is match with what i need.

thank you so much @v-zhenbw-msft