Calculating average by year

[dc189]

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). Column C has one average value of sales per sq.ft. for all rows containing 2010, then a different average value for all rows containing 2011 and so on. I created Column C with the help of a pivot table in Excel.

My question is - How do I create column C in Power BI as a calculated field?

Thank you,

dc189

[Greg_Deckler]

I would create a measure which is basically:

Yearly average Sales per sqfeet = AVERAGE([Sales per sqfeet])

Another way would be to have a table of years related to your fact table and create the custom column there like:

Yearly average sales per sqfeet = CALCULATED(AVERAGE(Table[Sales per sqfeet],RELATEDTABLE(Table))

[dc189]

@Greg_Deckler thanks for your message. Unfortunately the second formula doesn't work. Power BI gives an error saying "the syntax for CALCULATED is incorrect".

The table I shared with you is part of a bigger table. What I basically is to recreate the "Yearly average Sales per sq.ft." column as below:

Year----Sales per sq.ft.------Yearly avg Sales per sq.ft.

2010---60----55

2010---50----55

2011---40----35

2011---30----35

2012---20----30

2012---40----30

I created a new table with just one column 'Year' and with the values 2010, 2012, 2013, 2014 and 2015.

Now how do I relate this Year table with my original table to get the "Yearly avg Sales per sq.ft." column as shown?

Thanks,

dc189

[v-huizhn-msft · Accepted solution]

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]

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]

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]

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