Trying to create a budget table - process is timing out

[JRHans09]

I am trying to create a budget table that leverages existing data from previous years to project averages across multiple parameters:

1. Year

2. Month in Year

3. Branch

4. Cost Center (Line of Business)

5 Sales Rep

This is working, however when I add one more parameter, Revenue Type, the process is timing out.

This code works:

*Sales Rep data redacted

Then, when removing the comment tags to apply RemProd[RevType] to the CrossJoin section, as here, the process times out:

The below formula times out:

Here is the timeout error message:

Task Manager screen shots:

And here:

Here are the specs for my machine:

Is this issue related to my machine capabilities? Is there any way to accomplish the same goal with a different table formula or otherwise?

Thanks in advance.

[johnt75]

This code is generating every possible combination of the columns you've specified, even combinations which aren't possible - presumably a branch is associated with a limited number of cost centres, and a sales person with a limited number of branches.

In the ideal situation where you have a chain of 1-to-many relationships you can SUMMARIZE on the last many table to get just the valid combinations. e.g. if you have many sales people to 1 branch and then many branches to 1 cost centre you could do

Base Table = SUMMARIZE( 'Sales person', 'Sales person'[sales person ID], 'Branch'[Branch ID], 'Cost Centre'[Cost centre ID])

and use that with either CROSSJOIN or GENERATE.

Also, depending on how large your date table is it might be worth filtering that before doing the CROSSJOIN - no point generating the additional rows just to filter them out.