Forum Discussion

kouter's avatar
kouter
Advocate 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 = 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.
Need Help

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    3 years ago

    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 

  • kouter's avatar
    kouter
    Advocate 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