Forum Discussion
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). 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
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
6 Replies
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))
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
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
