Adding a percentage change to a matrix

[davidmais]

I have created a matrix with road deaths in GB in 2013 and 2014 by road user type: 

I want to add a column to the matrix to show the % change between years for each road user type e.g. (339-331)/331=2% for motorcyclists. 

There doesn't seem to be any obvious way of doing this so I would appreciate any help!  

[crvera]

Same problem here. In my case my data have 2 columns (one with Date data the other values). Then, making the matrix I put Month in rows and Year in columns. I want to do the same diference in %. There must be something.

[crvera]

Same problem here. In my case, my data are 2 columns (column with Date data and another with Values) then making the matrix I put Month in rows and Year in columns and I want to do the same diference in %. There must be something !

[v-sihou-msft]

@davidmais

For your requirement, it can be achieved whitin Reporting Services (See a similar thread). However, in Power BI Desktop, it can neither add column in Matrix control nor embed code to do calculation between column group. Since can only create calculated column or measure the calculate on dataset level, I don't think there's better way than change source table structure (give one column per year) as @SabineOussi suggested.

Regards,

[SabineOussi]

Hi david,

You have your years as columns ie a column for year 2013 and another for year 2014? Or as fileds ie a column under the name years whose values are 2013 or 2014?

If it's the first case, then it's quite easy. Create a calculated column like the following (Table1[2014]-Table1[2013])/Table1[2013] and have its format as % then add it to the matrix as a column.

If the years are values then you'll have to pivot the columns. Go to edit queries, select the years column, go to transform, pivot columns. Choose to pivot by the number of accidents rather by the type. You will now have a separate column for each year and you can do the steps described in case 1.

If anyone has a better solution, please share it.

Thanks.