Please help, to get multiple items selected in pivot table's Filter

[Rainbow_P]

Good day!


Can you please help me with a question on Pivot table Filters?

Taking below as a simple sample. There are (Multiple Items) in the Month ("1987-09" and "1987-10").

Is there any way/formula that I can get the filter set? (to return the values as "1987-09" and "1987-10")

Thank you.

Book3.xlsx
	A	B	C	D	E	F	G
1	Town	Department	Month			Month	(Multiple Items)
2	East	Beer and Wine	1987-08
3	East	Health and Beauty	1987-09			Row Labels	Count of Department
4	East	Meat	1987-09			East	4
5	East	Produce	1987-10			South	1
6	East	Seafood	1987-09			Grand Total	5
7	South	Produce	1987-10
Sheet2

 

[alansidman]

Is this what you are looking for? Not quite sure what you are asking. Suggest you show a mocked up solution

Row Labels
East
1987-08
1987-09
1987-10
South
1987-10

 

[Rainbow_P]

@alansidman, thank you for your reply.

I meant, what's the way to return the values selected (in cell G1 in my original post).

Thanks again.

 

[alansidman]

Why is East showing as 4 in your mock up (Col G) when there are 5 East Items? And only 3 unique dates? Please unconfuse me.

 

[Rainbow_P]

Good morning @alansidman, thanks for the follow-up.
It's because the pivot table has a Filter for chosen multiple months for "1987-09" and "1987-10".

I want to know what's the way to get to know the "1987-09" and "1987-10" (preferabbly a workable way in another sheet, for example a formula).

Thanks again

 

[alansidman]

Does this presentation work for you?

Book6
	A	B	C
1	Town	Department	Month
2	East	Beer and Wine	1987-08
3	East	Health and Beauty	1987-09
4	East	Meat	1987-09
5	East	Produce	1987-10
6	East	Seafood	1987-09
7	South	Produce	1987-10
8
9	Town	Month	Count
10	East	1987-08	1
11	East	1987-09	3
12	East	1987-10	1
13	South	1987-10	1
Sheet1

If Yes, then use this Mcode from Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Town", "Month"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[Rainbow_P]

Good day, @alansidman, thanks for your attention and effort again!

However, it's not the solution I am looking for.

Does this presentation work for you?

Book6
	A	B	C
1	Town	Department	Month
2	East	Beer and Wine	1987-08
3	East	Health and Beauty	1987-09
4	East	Meat	1987-09
5	East	Produce	1987-10
6	East	Seafood	1987-09
7	South	Produce	1987-10
8
9	Town	Month	Count
10	East	1987-08	1
11	East	1987-09	3
12	East	1987-10	1
13	South	1987-10	1
Sheet1

If Yes, then use this Mcode from Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Town", "Month"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"