Pivot table and pivot chart based on dynamically filtered table.

[mogupe]

First I need to clarify that I’m limited to use PowerPivot in Excel (no desktop) but I have some flexibility in generating my own customized queries against a SQL Server to bring to the PowerPivot data model. I'm experienced with excel but I feel lost on my first couple of weeks trying PowerPivot.

I need to provide a report to senior management in which selecting a project(s) will show the workload on the resources allocated both on the selected projects as well as the not selected projects. A slicer with the resource names will allow the analysis by person. The graph below might give you better idea of the issue.

I managed to get on measures the values of the work by in the selected project and the others which I use for the pivot chart. But I struggle how to present the data on a pivot table so the selected project AND all others are shown with their respective values. If someone can guide me towards the right direction that will help me. Currently I am blank on ideas and have no clue how to approach it. I can do it with VBA but I really want to start with PowerPivot.

Thanks for any tip!

[austinsense · Accepted solution]

In a nutshell you want everything that's been selected ... that's easy, that's just the regular measure, which you've got ... and everything that's not been selected ... EXCEPT( ALL(TableName[ColumnName]), VALUES(TableName[ColumnName]) ) ...

You can think of EXCEPT as saying show me ALL of the values of this column EXCEPT the values that have been selected in this report.

Selected Resources = [Measure]

Not Selected Resources = CALCULATE( [Measure], EXCEPT( ALL(TableName[ColumnName]), VALUES(TableName[ColumnName]) ) )

[Ashish_Mathur]

Hi,

There is no graph visible in your message.  Please repost.