0

I'm new to Power Pivot, sorry if the question is simple but i've been searching for hours already and i can't find an answer to this. The table below is from Power Pivot's Manage. The total Sum of (CoveredAndGapTotal) is 147. The table below is filtered therefore my CoveredAndGapTotal's sum (SumCandG) is 22. Is there a way to convert SumCandG to column? Because when i put the same formula (SUM([CoveredAndGapTotal]) in the column (Calculated Column 1), the return value is 147 and not 22. It sums up all the data regardless of the filter.

enter image description here

Community
  • 1
  • 1
Chester Lim
  • 459
  • 7
  • 19

1 Answers1

2

This is not possible, a calculated column is not related to filtering. That's why it's needs to be a calculated measure. When you are using this in an Excel pivot table, you will see that you can use these to calculate.

What result are you expecting in the pivot table in Excel?

WimV
  • 1,005
  • 6
  • 11
  • I'm expecting 22, like the value from (SumCandG). I need it to be in column form and also this column should be available in my (Power Pivot - Manage) because i need the data for my power view. Should i just give up on this? Thank you for the reply. – Chester Lim May 03 '16 at 21:38
  • Do you need have dynamic filtering? anyhow you can use a calculated measure in powerview, so that's not the issue. http://www.wimv.be/pbi/se20160503.png – WimV May 03 '16 at 21:58
  • I will use your attached image as reference. Is there a way where i can make a column that contains just the Total of NrOfCalls? That's what i need. John 20 Karen 20 Kerry 20 – Chester Lim May 04 '16 at 03:22
  • yes. If it's depended of filter context: NrOfCalls :=countrows(MyTable) If it's not depended of filter context: NrOfCalls_noFilter :=calculate(countrows(MyTable),all(MyTable)) – WimV May 04 '16 at 09:15
  • The filter that i'm talking about is the dropdown in the image above. Maybe that is a different filter from what you are expecting? Because when i do countrows(MyTable), it just returns all the rows from the table. I'm assuming the filter i'm talking about is actually not a filter rather a grouping. But from the image above, is there a way for the 22 to be inserted as a column above? Thanks – Chester Lim May 04 '16 at 09:29