slicers for custom calculated field in excel pivot table/pivot chart
I have a multiple calculated fields in my excel pivot table.
The field is in the values part of my pivot table and I sum them. I want to add a slicer to select them or not.
i.e. I want my slicer to select basically hide and show AA1,AA2,...,AA8 in the chart and table.
This is normally easy if the field already exists in the table, but because this is a calculated field I can not work out how to do it.
My data is attached.
Re: slicers for custom calculated field in excel pivot table/pivot chart
It can be done with Power Pivot but I don't think that is possible in your Excel Version
You need to use Power query to unpivot your original data in order to have AA1, AA2 as records in row. After that you need to create a measure as ratio between values and "n" column and create a Pivot Table from PowerPivot data model.
The query:
HTML Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type datetime}, {"n", Int64.Type}, {"AA1", Int64.Type}, {"AA2", Int64.Type}, {"AA3", Int64.Type}, {"AA4", Int64.Type}, {"AA5", Int64.Type}, {"AA6", Int64.Type}, {"AA7", Int64.Type}, {"AA8", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Date", "n"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
Bookmarks