Forum Discussion

Pillic's avatar
Pillic
Icon for Helper II rankHelper II
5 years ago
Solved

Help for simple measure

Hello Community,

 

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

 

A product_table that holds all information and is distinct:

ProductHeightPrice
AA102
BB203
CC302
DD155

 

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

ProductTestResults
AATest Strength29
AATest Strength28
BBTest Density2300
CCTest Temperature24
DDTest Strength29

 

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:

ProductStrength Test
AA29
AA28
DD29

 

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.

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

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

     

3 Replies

  • 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's avatar
    Anonymous
    Not applicable

    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's avatar
      Pillic
      Icon for Helper II rankHelper II

      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.