SOLVED

[SOLVED] Peculiar filtering in a pivot table I can solve

Copper Contributor

Hello,


Here is a simplified example of my problem (see sample in attachment). I have several tables. Each one contains data for one month. In the screenshot, the table (on the left) stands for the synthesis created with Power Query.

 

I need to create a pivot table to have an overview of ids performance along the months. I want to have a slicer (or any other solution) to dynamically filter the ids according their presence in the chosen month (here for February for instance).

 

Currently, the slicer filters the months (only February column will be shown). That’s not what I need. I want to filter the ids according to months. Show only ids that are present in the given month but show also their perfs for the other months if there are. The screenshot shows what I want to accomplish but the sample file in attachment is even clearer.

 

I wrack my brain with power query and power pivot but I’m stuck. With power pivot, I can not create relation with another table that would contains only id and month because ids are not single.

 

How could I do that ? I can use power query and power pivot if needed.

 

excel.png

 

7 Replies
I'm not sure what the issue is, does the slicer not do what you need?
The slicer filters the months (only February column will be shown). That’s not what I need. I want to filter the ids according to months. Show only ids that are present in the given month but show also their perfs for the other months if there are. The screenshot shows what I want to accomplish. I don’t know how I can be clearer.

@FredDF Perhaps the attached file can help you on your way. I've added two helper columns and an extra pivot table (hidden behind the slicer).

Riny_van_Eekelen_0-1694007452878.png

 

Thanks but selecting the month in the slicer doesn’t filter anything the pivot table. It only filter the hidden pivot table and change the values in the source table. But it’s a start. I’ve updated my thread with an excel file as attachment.

@FredDF but you need to refresh the bigger pt. 

best response confirmed by FredDF (Copper Contributor)
Solution

@Riny_van_Eekelen Actually, I found an elegant solution using measure. Check it out here.

@FredDF Good for you!! Not really interested in the solution,

 

But, it's not good practice to cross post your problem in multiple fora, like here, Answers, ExcelForum (and perhaps others as well) and not telling us about it UP FRONT.

1 best response

Accepted Solutions
best response confirmed by FredDF (Copper Contributor)
Solution

@Riny_van_Eekelen Actually, I found an elegant solution using measure. Check it out here.

View solution in original post