Percentages over specific columns

Question

Percentages over specific columns

jen 834

SSC Rookie

Points: 27
More actions
September 5, 2011 at 10:33 pm

#390344

I'm not even sure how to describe this issue, but I hope I can demonstrate my problem. Any advice or suggestions on what I could read up on would be tremendously appreciated.
Here's the deal...I have a report that needs to look something like this:
I've highlighted 3 fields to demonstrate what I'm trying to accomplish. Those 3 fields are what needs to add up to 100%. It's not that I want the sum over the scope of all price levels, I want the sum over the price levels that are specifically "A-B-C". Then when it rolls up, the sum of (for instance) all the EPG categories should be at 100%. (Sure hope this is making sense...I'm unfamiliar with doing a report like this, but the client is firm that this is the way it needs to be).
I've researched summing recursively, but I don't think that's what I need...correct me if I'm wrong. I had a working version where I wrote some fields into the query that would sum up those totals for me, but then when I found out that I'd also have to be able to drill down by fiscal year/quarter/month, that no longer would do.
The only option I can think of at the moment (and trust me, I've noodled over this for way too long, and it's blemished my 3-day weekend, lol) is that I should again add the fields into the query, but this time it would be sums for each price level by year, price level by quarter, price level by month, segment by year, segment by quarter, and segment by month. I think that would work...but it just strikes me that there's got to be a better way.
Please don't hesitate to tell me if this doesn't make sense, you need more information, etc. Any help you can provide would be appreciated beyond belief.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Brooking Ten Centuries Points: 1373 More actions · Answer 1

Jen,

I'm not fully understanding the requirement. The highlighted columns do add up to 100% (Q4 adds up to 99.99%, but probably due to a rounding error). Are you saying that when the EPG segment isn't expanded the values are not displayed as you expect?

What does the report look like when no segments are expanded?

If you were to also post your expected results, it may help me better understand the problem.

Dave

jen 834 SSC Rookie Points: 27 More actions · Answer 2

so sorry, let me try to clarify:

For starters, here is what the report looks like fully rolled up:

and here is is drilled down one level:

(with the fields highlighted in yellow that add up to 100%

The screenshots are of a working report (I unfortunately don't have a way to view its source, this is just something already on the company sharepoint), and I'm trying to figure out how to replicate it.

Dave Brooking Ten Centuries Points: 1373 More actions · Answer 3

Jen,

I think the answer may lie with the Inscope function. This isn't something I've previously used, so I can't offer any insights into its usage. I'll have a play with it in the next couple of days to see if it does fit the bill.

Dave

jen 834 SSC Rookie Points: 27 More actions · Answer 4

Well, what I've hacked together does use the inscope...I'm still not convinced that it's the best way to go about it, but at least it'll meet the deadline 🙂

I altered the query so that it pulls together the sums for each area that I needed, then it just checks in the expression for what scope it's in and uses that particular sum. If you can figure out a better way, I'm all ears. I'm trying to learn by others that are more experienced, so any tips/tricks are always appreciated.