Forum Discussion

kouter's avatar
kouter
Icon for Advocate I rankAdvocate I
3 years ago

Rewrite query to alter calculation

Hi,   I have the following measure which calculates the average of a column per distinct value of another column and then multiplies these averages with eachother.   Measure = VAR total_weight...
Need Help
kouter's avatar
kouter
Icon for Advocate I rankAdvocate I
3 years ago

Hi TomMartens 

 

Thank you very much for your reply. Apologies for being unclear.

 

I have the following measure (I'm ignoring the weightings)

 

Measure =
AVERAGEX(
VALUES(Sheet1[GENDER]),
CALCULATE(AVERAGE('Sheet1'[Value]),FILTER(Sheet1,'Sheet1'[Attribute] = SELECTEDVALUE('Sheet1'[Attribute]))))
* AVERAGEX(
VALUES(Sheet1[GENDER]),
CALCULATE(AVERAGE('Sheet2'[Value]),FILTER('Sheet2','Sheet2'[Attribute] = SELECTEDVALUE('sheet2'[Attribute]))))
 
This measure will group by gender, and filter the respective tables on there selections

my filter1 selection is height, and filter2 is rp, using a example with the gender Male

 

the measure will isolate rows that has height attribute for male which give values of (1,3), average these values resulting in 2. It will then isolate rows in sheet2 with rp attribute and give values of (1,3) average it again resulting in 2. Finally, it will multiply these two averages which gives 4.

 

I want to isolate the values in both tables first, (1,3) and (1,3), multiply them on row by basis resulting in (1,9) and then average these values which will give me 5.

 

I hope this is a bit more clear.

 

https://drive.google.com/file/d/1oDDJYBgTyPxSfuENRNdN3LTQwVGXGXin/view?usp=share_link 

 

https://docs.google.com/spreadsheets/d/1RbP29rh32Rg9mzr5DufRU2bS8K57UHxz/edit?usp=sharing&ouid=106780951647461836424&rtpof=true&sd=true