PowerPivot Pivot Rows Collapse When Add Measure To Pivot Table

[goss]

Hi all,

Using Excel 2010.

I inserted a new Pivot Table from the Data Model
I dragged Supervisors to Row Labels
I then tried to add a Measure I created to the Values Field
As soon as I did, the entire Pivot Table collapses to just Header Row

The same process is working for other items in other Pivot Tables with different items in the Row Labels such as Clients and Departments.

I checked the relationship in Diagram View, and in Manage Relationships. Everything is correct between the fact and dim tables.

What else can I check on?

thanks
w

 

[RoryA]

What's the measure? Does it return Blank() for any circumstance?

 

[goss]

What's the measure? Does it return Blank() for any circumstance?

I do have BLANK() is some measures, but not in the current Measure:

Selected Month Portion Score:=CALCULATE([Total Portion Score],FILTER(dimCalendar,dimCalendar[ReportMonth]=[SelectedReportMonth]))

Thanks
w

 

[RoryA]

Should there be a function applied to the [Total portion score]?

 

[goss]

Should there be a function applied to the [Total portion score]?

Thanks Rory,

I don't believe so, though I could be wrong.

I defined the Measure for the Total Column:
TotalActualScore:=SUM([ActualScore])

I'm using a disconnected table for a slicer and getting that in a Measure:
SelectedReportMonth:=MAX(dimMonths[ReportMonth])

Then using that measure to evaluate the Filter()
FILTER(dimCalendar,dimCalendar[ReportMonth]=[SelectedReportMonth]))

And then Calculating the Measure of the entire Column after the filter
Selected Month Actual Score:=CALCULATE([TotalActualScore],FILTER(dimCalendar,dimCalendar[MonthNumber]=[SelectedReportMonth]))

All of this seem to work and aligns with what Rob Collie wrote in his book on pg 97.

This approach seems to work for my first two Pivots, but then failed on the 3rd.
I tried to copy paste the Pivot since the only change was on the Row Label.

I tried to drag the correct row label to the Row Label area below the old Row Label and
then remove the old Row Label - did not give the correct results.

I went back to the PowerPivot Window and tried to insert a new Pivot - that did not work either.

Thanks
w

 

[RoryA]

OK - it wasn't clear that was a measure. (I assume the formula is similar to what you just posted?)

Any chance you can put a file up on Skydrive/Dropbox/other so I can take a look and see if there's anything obviously wrong?

 

[goss]

OK - it wasn't clear that was a measure. (I assume the formula is similar to what you just posted?)

Any chance you can put a file up on Skydrive/Dropbox/other so I can take a look and see if there's anything obviously wrong?

Sorry, It's all proprietary stuff. Security most likely has me blocked from all cloud drives as well.
I did get working on 3 simple tables:
dimMonths
dimCalendar
factScores

I really think it has something to do with what I am doing

I am rebuilding everying from the gound up.
1. Create a new Access Database
2. Create dim tables, Setup Tables first with KP autonumber, paste desired text into into Column 2
3. Import each dim table into the Data Model and Create relationship
4. Create all Measures
5. Try to build one Pivot with Measures
6. Copy Pivot to an area on the worksheet
7. Remove any items from Row Labels
8. Drag new items from The Power Pivot Field Pane to the Row Labels Area

So far I have buit 3 Pivot Tables and all are correct.

I think that with my last approach, I was losing the connectivity to the discoonected table that drives the Slicer (If that makes sense)

Thanks for your help.
w