Creating an Average Column/Measure Combining multiple Specific rows over two tables

[ScarlettBebb]

Hello all 🙂 

I'm hoping someone can help me with this problem. I have the dataset in the screenshot below. I need to create a column (or measure) it doesn't matter which one as long as I can plot it in a visual later on. I want to display Average Signal of a particular group which would be W1-W96

And then further in another Table I have what looks like this (real one not shown for data privacy sorry I just made up another version in Excel but the real one is in PowerBI) 

So as the end result I would need the average signal of a product (for example HHHHGGGG Lot 1 for QA1 in group W1-W96)

Thank you so much in advance if you give this a go. 

[Anonymous · Accepted solution]

HI @ScarlettBebb,

You can write a measure formula to use these fields as group to filter on the aggregate calculations:

formula =
CALCULATE (
    AVERAGE ( Table1[Lot] ),
    ALLSELECTED ( Table1 ),
    VALUES ( Table1[Part Number] ),
    VALUES ( Table2[Well Position] ),
    VALUES ( Table1[QA Sample Point] )
)

If the above not help, can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng