Filtering GL Accounts Based on Percentages

[ChrisParticipant Started: 1Replies: 3Total: 4]

Hi all,

I’ve got a bit of a quandry I need some help/perspective on. I didn’t find this specific question in any of the blog posts or by searching the forums, but I do apologize if it has been answered already.

I’m trying to create a Financial report that has GL codes that are attributed against multiple divisions, e.g., three divisions = A, B, C, with a few different GL codes like “Dedicated Expense A” and “Expense ABC 1” where “Dedicated Expense A” = 100% in A and “Expense ABC 1” is perhaps 50% in A, 30% in B, and 20% in C.

Filtering on all the GL codes (and any aggregated sum data) for GL codes that have a percentage = 100% A or 100% B is quite simple of course. Summarizing the totals for each group attributed against the percentages is also quite simple. What I need to do though, is summarize all of the amounts with the percentages for any GL codes within A, B, and C within a single number, i.e.,

Revenue A (100% A) + Dedicated Expense A (100% A) + Pct Expense AB (50% A, 50% B) … etc. = SUM(all $$ for current filters applied).

So, I might have Divisions A and B filtered, so I want the total sum of all GL codes with dollars attributed (0-100%) to Divisions A and B.

And I need to attach a [disconnected (I think)] slicer to it. I’m pretty sure the disconnected slicer is the only way to do it, but I can’t quite figure out where the disconnect (oh yes…I went there) is.

I made myself a new workbook with some dummy simplified numbers and GL categories to putz around with. If anyone has any input, that would be fantastic. I’m quite stuck with this one and I’m new-ish to powerpivot.

Thanks!!!!

Chris

Sorry if anything looks odd, I’m having a lot of trouble posting on the forum

Attachments:

You must be logged in to view attached files.

[ChrisParticipant Started: 1Replies: 3Total: 4]

I was having trouble attaching multiple files, so here’s a quick scan of a sketch I made up. Might be clearer.

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Chris,

The model was too complicated: it was pre-pivoting a section of an unpivoted table.

Unpivoting in Power Query is the proper action.

Please see attached workbook.

Best regards,

Tom

Attachments:

You must be logged in to view attached files.

[ChrisParticipant Started: 1Replies: 3Total: 4]

Tom,

Thank you so much! I was just expecting a bit of a nudge in the right direction, not an all-expenses-paid round-trip vacation! Thanks!

Makes perfect sense now. I should definitely be able to apply this to the more complicated actual financial model. My thinking was actually born out of simplifying the financial numbers, instead of simplifying the PowerPivot model. I was backwards, obviously.

I’m missing something though, probably super simple. I’d like when I filter for Division A for all GL codes that have no percentage dedicated in that division to disappear form the pivot, but they’re sticking around. I know this is a super simple one, but I’m just not seeing why.

Thanks again for the help and have a great weekend!

Chris

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Chris,

Select a cell in the pivot, when the PivotTable tools context menu appears in the Excel ribbon, choose the Analyze tab, then go to the PivotTable group and launch the Options dialog. On the Display tab of the Pivot Table Options dialog, uncheck “Show items with no data on rows”, then click “OK” to close dialog and save your selection.

Let me know if that makes the pivot do what you wanted to do.

Tom

[ChrisParticipant Started: 1Replies: 3Total: 4]

Yep, definitely did! Thanks! I knew it’d be something simple – still learning a lot. Appreciate your help.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Chris,

Looking forward to working with you again in the PowerPivotPro forums!

Tom

[Author]

Posts