Forum Discussion

dc189's avatar
dc189
Helper II
9 years ago
Solved

Calculating average by year

Hello,   I have a simple dataset (https://www.dropbox.com/s/euehk38ihd5ki3h/Book1.xlsx?dl=0) with 3 columns - Year (column A), Sales per sq.ft. (Column B) and Average sales per sq.ft. (Column C). C...
Tips and Tricks
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi dc189,

    There is no CALCULATED function in PowerBI, it should be CALCULATE.

    Why do you relate original table to a date table, you can create a calculated column in original table and get expected result.

    Yearly avg Sales per sq.ft. = CALCULATE(AVERAGE(Table3[Sales per sq.ft]),ALLEXCEPT(Table3,Table3[Year]))


    Please see the result in the following screenshot.



    Please let me know if you have any other questions.

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi dc189,

There is no CALCULATED function in PowerBI, it should be CALCULATE.

Why do you relate original table to a date table, you can create a calculated column in original table and get expected result.

Yearly avg Sales per sq.ft. = CALCULATE(AVERAGE(Table3[Sales per sq.ft]),ALLEXCEPT(Table3,Table3[Year]))


Please see the result in the following screenshot.



Please let me know if you have any other questions.

Best Regards,
Angelia

dc189's avatar
dc189
Helper II
9 years ago

Thanks Angelia for your help! Your formula was helpful and led me to the following formula that also worked for me:

 

Yearly Avg Sales per sq.ft. = CALCULATE(AVERAGE(Table[Sales/sq.ft.]),FILTER(Table,AND(Table[Year]=EARLIER(Table[Year]),Table[Size]<>0)))

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi dc189,

    I am very gald you have resolved your issue, please mark the helpful reply as answer, which will help more people.

    Best Regards,
    Angelia 

    • dc189's avatar
      dc189
      Helper II
      9 years ago

      Hello v-huizhn-msft,

       

      I also need to create a dashboard with direct query connection (not import connection). The problem is the Calculate function you suggested does not work with direct query. Is there an alternative function to Calculate so I can calculate the yearly average sales per sq.ft? Or can you make Calculate work with direct query?

       

      Thanks,

      dc189