Multiple ALLEXCEPT in same CALC?

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

I have a financial statement file where I show sales and all expense by period and by branch location.  I also calculate a % of revenue percentage column where I show x expense is 10% of total Sales.

I have all sorts of detail (vendor, doc#, etc…) collapsed so I can drill in and see detail as needed.

In order to get the % revenue calc to work, I currently use CALC ALL(vendor), ALL(doc) etc..

What I would like to do is just use ALLEXCEPT(Location) && ALLEXCEPT(Period).

Is this possible?  To give it two filters to respect in the filter rather than everything (all the other details) it should ignore in the filter?

Many thanks!

 

Attachments:

You must be logged in to view attached files.

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

Hey Mark,

The syntax for ALLEXCEPT is

ALLEXCEPT(<table>,<column>[,<column>[,…]])

The workbook example has only one table in Power Pivot, so given the syntax and only one table, two uses of ALLEXCEPT is one too many. Why not try ALLEXCEPT ( Stmt, [Location], [Period] )?

Let me know if you have a multi-table model instead of a single table model.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

I simply didn’t know the correct syntax.  Thanks again!

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

Mark,

Wanted to congratulate you on making the Leaderboard for recent replies in the Power Pivot Pro forums. If you get a minute, take a look on the middle-right-side when you return to the site.

Attachments:

You must be logged in to view attached files.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

That’s awesome!  Thanks for letting me know.  I really do enjoy the site, and the teaching approach works well for me.

I am an evangelist for sure 🙂

Tom, can you tell me if there is a outline for the online $349 course the site offers?

Keep up the great work!

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

Hi Mark,

Following is an un-official outline about the course created from my notes (you probably can get an official outline by calling 440.719.9000 or emailing [email protected]).

A copy of the ebook, DAX Formulas for Power Pivot

About 40 – 45 workbooks (some teach technique; others contain practical business applications; some are just jaw-dropping examples of what Rob has learned)

About 90 course modules, all taught by Rob Collie (20+ hours of video), with topics such as:

Warmup & Fundamentals

Measures & Calculated Columns

Thinking like the Power Pivot Formula Engine

Deep Dives into Functions

Working with Multiple Tables

Disconnected Slicers and Parameter Tables

Time Intelligence (Out-of-the-box and Custom)

Aggregate Functions

Advanced Calculated Columns

Cube Formula Reporting

Many-to-Many

X Functions

Conditional Formatting

Getting past roadblocks and avoiding pitfalls

Performance and Optimization

Using Macros and VBA to Streamline Presentation and Maintenance Tasks

Course fees include year’s access to course and materials, as well as email access to instructors.

—

I thoroughly enjoyed the course–not only for the skill sets developed, but the course also builds an I-can-do-that-because-I-have-watched-and-followed-Rob-do-that confidence regarding Power Pivot specifically and about learning generally.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Thanks for sharing that outline Tom.  I hope to take this course before year end.

[Author]

Posts