Adding percentage column of grand total in Cross table

[Gabriel Yeong]

Hi all, I have the below sample data and wish to add a column in the cross table to show the % of failures over all statuses in that partiular year.

The result I wish to have is in the attached screenshot highlighted in Yellow. I am trying to figure out how to add this directly in the cross table as a column instead of adding a calculated column in the data table and summing up separately.

Also, can anyone explain what the 'over' function does

 

 

Thanks

 

 

 

 

YEAR

STATUS

 

 

2018

OTC

 

 

2017

InProgress

 

 

2017

InProgress

 

 

2017

OTC

 

 

2016

Late

 

 

2016

OTC

 

 

2016

OTC

 

 

2016

OTC

 

 

2016

OTC

 

 

2016

Late

 

 

2016

Late

 

 

2016

OTC

 

 

2016

OTC

 

 

2016

InProgress

 

 

2016

InProgress

 

 

2016

OTC

 

 

2016

InProgress

 

 

2016

InProgress

 

 

2015

OTC

 

 

2015

OTC

 

 

2015

Late

 

 

2015

Late

 

 

2015

OTC

 

 

2015

OTC

[Shashank Gurnalkar]

One way of doing it is to copy the existing table and pivot it as shown below.

 

Insert a Cross table and configure it to show Year on the Vertical axis, and Column Names on the Horizontal axis. Use the following custom expression in the cell values.

Sum([inProgress]) as [inProgress],

Sum([Late]) as [Late],

Sum([OTC]) as [OTC],

Sum([inProgress]) + Sum([Late]) + Sum([OTC]) as [Grand Total],

Sum([Late]) / (Sum([inProgress]) + Sum([Late]) + Sum([OTC])) as [Late%]Format the [Late%] column to show the data in percentage.

The final result will look like this:

[Gabriel Yeong]

Thank you very much! That's very helpful. Is there a way to still retain the "unpivot" data when I change to Table View to see all the rows

[Shashank Gurnalkar]

Your main table (unpivoted table) is intact because we copied this table and then pivoted the copied table. So as and when new data comes in the main table, it will be reflected in the copied table (pivoted table) accordingly.