Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Weighted average from other table

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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    5 years ago

    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.

2 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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