Pivot table - Slicer question

sfitzpat11

New Member
Joined
Jan 24, 2017
Messages
12
Hello, I need help with a pivot table to compare year to date sales between two different years. My data is arranged as follows:
* Column A contains the different products
* Column B contains the year of the sale
* Column C contains the country the product was sold to
* Column D contains January sales and E for February sales and so on
For the pivot table I want the 1st column to contain the product. The second column to be the first year and the third column to be the second year based on what I select from the Year slicer. I want the sales total for those years to be based on the months that I select but I cannot figure out how to created the slicer for the months since each month has its own column of data. I have been able to create this but only if I put the month field into the "Values". I want to do this via a slicer and I also do not want to display the monthly sales in the PT, only the totals. Please help.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,487
You'll need to reorganise the data so you have a column for month name and a single column for sales.

Then just have Product in Rows, Year in Columns and Sales in Values and add the 2 slicers.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,086
First Power Query to get table for Pivot
SourceResult TBL
ProductsYearCountryJanuaryFebruaryProductsYearCountryMonthValue
item12018France2245item12018FranceJanuary22
item22919Belgium3440item12018FranceFebruary45
item32020Germany6566item22919BelgiumJanuary34
item42018Spain9119item22919BelgiumFebruary40
item32020GermanyJanuary65
item32020GermanyFebruary66
item42018SpainJanuary91
item42018SpainFebruary19

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    UOSC = Table.Unpivot(Source, {"January", "February"}, "Month", "Value")
in
    UOSC
then Pivot with a Slicer
pivotandslicer.png
areas.png



Green table is to show only how it looks but it's not necessary load this table into the sheet
To create Pivot Table you can use transformed table directly from the Query of PQ
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,068
Messages
5,545,800
Members
410,708
Latest member
SanTrapGamer
Top