Keeping items from aggregating totals in PowerPivot (2010)

[hghcmndr]

I'm more of a lurker than a poster, but I need perspective. I'll try to keep this brief:

I have five data sheets that PP uses to compile course, student, and textbook data. I started off green, with zero PP experience. Using Rob Collie's book, I've gotten 95% finished, but I need to take individual data, aggregate the data in a report (or the like), while keeping individual totals.

Using slicers, I can click on a course, text title, and semester and produce results for the course, number of texts in various states (e.g., lost, checked out, on shelf, needed or extra (depending on the class[es]). Slicers allow me to attach any book to any class, singly or in multiples, and that's what I need, day-to-day. A couple of times a year, though, I need to produce a list of every book, according to how many we have or need. Clicking multiple texts/courses on the slicers aggregates the data, when what I need is for each text's individual data to remain true to the course to which it is associated. A running total is not going to help me, but that's what I get when I select multiple texts/courses, as each text assumes I want each book for any/all classes. That's the way I set it up, and I need this flexibility, but I need to also produce accurate data for each book without filtering it by any courses.

I'm probably too close to this project at this point, and doubtless my over thinking it makes me unable to see what will probably be a simple solution. All I need is a list that has all texts with the data that is specific to them, but I cannot see a simple way.

If this sketchy description needs fleshing out with data, examples or such, I'm happy to provide these. As I mentioned, I'm new to the posting part and may well have missed some protocol or expectation. Patience would be appreciated.

sjp

 

[ImkeF]

Try adjusting your rows design: You cannot stop your measures from aggregating (according to what you have designed in the measure definition), but the fields in your row section will determine to which level they will be aggregated to.

So look out for a "detailled enough" field that matches your desired aggregation Level and put it into the row section of your Pivot table.

hth, Imke

 

[hghcmndr]

Thanks for responding. Unfortunately, I'm not seeing that I can do what you suggest with the way I've constructed things (could just be me). Maybe there's a new measure I can create? What is so frustrating, is that I already have all of the individual data I need working fine, with a slicer for text title and a slicer for course name that will link any text(s) to course(s). =calculate([Total Texts]-('Course Subtotals'[Aeries Enrolled]+[Lost Texts]+'Class Sets'[Class Sets]+[Repairs])) gets me all the individual results I need, but there's a whole lot that's been put together elsewhere that generates the data/measures needed to pull that off. I suspect that formula is useless to anyone without the context.

At this point, I have so much going on that I don't even know where to begin bringing people in who could potentially help. What I need is the PP equivalent of matching a title in one table to a title in another and returning a matching number of texts needed (as determined by an existing measure); then I need it to do the same for every text, and then list these results in a nice pivot or report . . . as it would in Excel if using a named range and a sumifs or an if function with nested sums.

The aggravating thing is that I can fairly easily do this sort of thing in Excel proper . . . but as I say, I need others to use this data without their having to become savvy with formulas, functions and the like. The previous Excel sheet is just too Excel tech-knowledge heavy for the folks who are being asked to use it, especially as it is not a static document. Data can be linked to the pivots and the rest needs to take care of itself.

I can screen-shot things, but without the underlying structure there's no real way to examine the processes involved. I think the best thing for me to do is to find a way to create specific course/text data for individual courses/texts (done) and then report each individual result in a pretty pivot/report/sheet that can be used to determine where we are short texts.

I will chew on this until I solve it . . . but I don't have that many years left.

 

[ImkeF]

You seem to have set up your data model in quite a special way. At least for me it's difficult to understand.
The only thing that comes to my mind is that this might help you: Mini Post #3: Measures Can Return Text! « PowerPivotPro

Good luck, Imke

 

[hghcmndr]

"Special way"? That has to be code for something!

Apologies for the screen grab. Here's the general output. Note that in column 2, Text Data, results for "Texts Remaining or Needed" are skewed . . . but note that all data in the Course Data and Shelf Data holds true for each Individual book. If it can keep things separate in PT 1 & 3, why not in PT 2? Keep in mind that the "Texts Remaining or Needed" data is correct if only one course is selected.

 

[hghcmndr]

Well, that was a major fail. Sheesh. Sorry. Can't seem to find a way that will let me post a pic. Guess I'm toast.

C'est la vie.

Thanks for all the replies.

 

[scottsen]

I need a picture or sample workbook on this one

 

[hghcmndr]

You may want to rethink that request seeing as I botched it so badly, previously. Assuming you want me to email you, directly, @ the tinylizard site?! Happy to comply if so.