Pivot table Year over year change

[Ferenc Puskas]

Greetings,

I am creating a dashboard with statistics of students at our language institution. I need help with year-on-year calculation in the pivot table.

My boss wants me to add a year-over-year change to that table (He is a beginner in Excell, so he does not know). My question is if it's feasible, in case the table is connected to slicers (image below, numbers randomized). So far all my attempts have failed. I have watched several tutorials, but haven´t seen any with pivot tables with slicers or similar layouts.

Would be glad if someone can let me know if it's doable with the current layout, or eventually refer me to some guide/forum thread on how to do it.

Thanks!

• The data set includes data of students from the last 12 years and information about, the institutes and the lenght of their course
• In the dashboard, I have created a pivot table with the numbers of applicants, accepted students, and registered students. The table is connected to slicers of academic years and the length of the course which is also connected to other graphs in the dashboard.

 

[AlanSidman]

Pictures are pretty worthless as they cannot be manipulated. Suggest you upload a sample file showing your data and then show a mocked up solution of what you are looking for. Help us to help you find a workable solution.

 

[Ferenc Puskas]

Thank you for the reply Alan,

Understood. The sheet is attached below.

I'm trying to insert into the table information on the annual change in post applicants (& accepted, enrolled). So far, I've tried the method through the option "show value as" => "Percentage from" => "year" => "previous". But it does not work. I've also tried calculated fields, but that did not function for me. I wonder what I am doing wrong or if it is even feasible for a pivot table with slicers.

My original data set has additional relations in the data model as I have more of the data. However, it does not function even in the attached sheet, which does not have any relation to the data model.
I would be glad for any suggestions.

 

[AlanSidman]

To do year over year, I see that you will need to put your years in the columns and then do simple comparison in the PT.
There may be a way in DAX in Power Pivot but not in my skill bank.

 

[AlanSidman]

I have relooked at this with a different idea. Instead of a Pivot Table, use Power Query to unpivot your data and then repivot it with a different layout than what you are looking at. See the output I have presented. With this presentation, you can easily do a YOY in the table exported from PQ to Native Excel. Here is the Mcode for PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Source_table"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"year", "Institute", "Programme", "number of applicants", "accepted", "enrolled"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"year", type text}, {"Institute", type text}, {"Programme", type text}, {"number of applicants", Int64.Type}, {"accepted", Int64.Type}, {"enrolled", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"year", "Institute", "Programme"}, {{"Applicants", each List.Sum([number of applicants]), type nullable number}, {"Accepted", each List.Sum([accepted]), type nullable number}, {"Enrolled", each List.Sum([enrolled]), type nullable number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"year", "Institute", "Programme"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[year]), "year", "Value", List.Sum)
in
    #"Pivoted Column"

The file is attached for you review.