PowerPivot - How to filter columns yet retain grand total

Question

in the table below, I would like to graph a subset of the columns, specifically the ones highlighted in green. however, I would like to compute the percentage based upon the grand total of the whole set. filtering columns rescopes the data set and hence the denominator.

I would like to show and graph columns "4" and "Excellent value (5)" while retaining the current "Grand Total." for the ct row, for example, the value would be 8/14 for column "4", and likewise for other cells.

as a candidate solution, I created a secondary table which references the table shown below. is this the cleanest way to accomplish the goal? it seems a bit kludgey and rather time consuming.

ultimately this graph will be rendered in sharepoint so it needs the dynamic interaction with the ssas data. I am assuming that this will be the case since my derived table is using the GetPivotData function which the parent table uses.

Answer 1

You need an ALL() to open out the filter context which in turn means that it doesn't matter which columns are selected.

Assuming you have a table called 'table' and a column called 'response_id' you could use something like:

= SUM(COUNTROWS(table)) / 
      CALCULATE(COUNTROWS(table), ALL('table'[response_id]))

If your real life problem is more complex then you might need to adjust the ALL() to include other columns or look at ALLEXCEPT() to do the opposite.