
Re: Calculate annualized cost based on given monthly cost data
Fawaz Abdul Azeez Jul 29, 2018 1:13 AM (in response to Andy Manuja)You can use
CrossTable(YearMonth, Cost, 2)
Than put a pivot chart on dash board with below measure
if(ColumnNo()=0,Avg(Cost)*12, Sum(Cost)) and column as YearMonth

Re: Calculate annualized cost based on given monthly cost data
Andy Manuja Jul 29, 2018 2:15 AM (in response to Fawaz Abdul Azeez)Hi Fawaz,
Thanks for your reply.
I did according to what you said but it gave me the total of individual component instead of the annualised value.
However, out of no where, I was able to get the answer using only the cross table and adding a new measure, "=Avg(Cost)*12" to a new field.
However, Thanks you very much for your respond and appreciate a lot if you could explain me the logic behind using of "ColumnNo()=0" statement.
Kind regards,
Andy

Re: Calculate annualized cost based on given monthly cost data
Bala Bhaskar Jul 29, 2018 3:07 AM (in response to Andy Manuja)ColumnNo() returns the number of the current column within the current row segment in a pivot table. The first column is number 1.
If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the interfield sort order.The interfield sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom.
In pivot table, instead of ColumnNo(), you can aslo use Dimensionality() like below.
if(Dimensionality()=0 or SecondaryDimensionality()=0,Avg(Cost)*12, Sum(Cost))
