Rewrite query to alter calculation

[kouter]

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 = 4

VAR weighting1 = 1/total_weight

VAR weighting2 = 1/total_weight

VAR weighting3 = 1/total_weight

RETURN

AVERAGEX(

VALUES(table1[col1]),

CALCULATE(AVERAGE(table1[col1])*weighting1,FILTER(table1,table1[col2] = SELECTEDVALUE(table1[col2]))))

* AVERAGEX(

VALUES(table1[col1]),

CALCULATE(AVERAGE(table2[col3])*weighting2,FILTER(table2,table2[col4] = SELECTEDVALUE(table2[col4]))))

* AVERAGEX(

VALUES(table1[col1]),

CALCULATE(AVERAGE(table1[col5])*weighting3,FILTER(table1,table1[col6] = SELECTEDVALUE(table1[col6]))))

However, I would like to multiply the columns (along with there weightings) with eachother before actually calculating the average, i.e. table1[col1])*weighting1*table2[col3])*weighting2*table1[col5])*weighting3 and then only calculate the average.

I hope my question is clear. Thank you very much for your help.

[kouter]

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=10678... 

[TomMartens]

Hey @kouter ,

I have to admit that I do not understand what you are trying to achieve.

Consider creating a pbix that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Explain the expected result based on the sample data you provide.

Regards,

Tom