Help for simple measure

[Pillic]

Hello Community,

I am struggeling by creating a measure and asking for help.

A product_table that holds all information and is distinct:

Product	Height	Price
AA	10	2
BB	20	3
CC	30	2
DD	15	5

And a test sample_table where multiple tests have ben created by each product:

Product	Test	Results
AA	Test Strength	29
AA	Test Strength	28
BB	Test Density	2300
CC	Test Temperature	24
DD	Test Strength	29

In the samples table multiple tests have been perfomed and they are all in the column Test

The relationship is 1 to n from product_table to sample_table - filtering both directions.

I now would like to analyze the samples by creating slicers in the sample table for the performed tests like "Strength Test" and have the average Height for all products in that test line.

Sliced result in samples_table:

Product	Strength Test
AA	29
AA	28
DD	29

How can I now calculate the average height of all sliced products? 

I tried to count the rows on the sliced sample_table like that:

Count of Values = CALCULATE(DISTINCTCOUNT(samples_table[Product]))

and then perform the 

Average = DIVIDE(SUM(product_table[Height]),[Count of Values])

 but that gives me the wrong results.

Your help is much appreciated.

[Pillic · Accepted solution]

After some research about star schema and dimensional tables I came up with this measure:

Average Height = AVERAGEX(sample_table, RELATED(product_table[Height]))

[Anonymous]

@Pillic 

In this case you no need to create a measure I believe. Just add the Product form sample table and height from the product table.

Since height is a Number type you can choose in the options (SUM, Average, Max, Min). In this choose average and it will resolve your requirements.

Thanks,

Karthik

[Pillic]

Hi Karhik,

you are right I need a measure as it doesnt make sense to join the tables for the default Average funktion as this is just a simplified sample. In real there are thousands of sample result rows and I would like to keep the distinct products table and the sample tabe seperated.