Forum Discussion

mogupe's avatar
mogupe
New Member
8 years ago
Solved

Pivot table and pivot chart based on dynamically filtered table.

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.

 

Model

 

Goal

 

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's avatar
    austinsense
    8 years ago

    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]) ) )

     

2 Replies

  • austinsense's avatar
    austinsense
    Impactful Individual
    8 years ago

    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]) ) )