PowerPivot Peformance Issues - Formulas too complicated?

[monsoonnut]

Hi
I have created a forecast accuracy report with a mixture of pivots and cube formulas which is working quickly and efficiently until I try to add in the final measure – this measure needs to be reported at SKU level and is calculated off other measures but never makes it to completion with excel crashing before the calculation is complete.
There are circa 6000 SKU’s in the model.
Does anyone have any advice regarding my measures? Could they be written in a more efficient way to enhance performance?

The measure is:

National Total Impact :=
IF (
[Nat Total Error]
- ( ( [Nat Total ALL F-A] - [NatTotABSErr] )
/ ( [Total ALL A] - [SumTotDem] ) )
= 0,
BLANK (),
[Nat Total Error]
- ( ( [Nat Total ALL F-A] - [NatTotABSErr] )
/ ( [Total ALL A] - [SumTotDem] ) )
)
-------------------------------------------------------------------------------
The measures used in this first measure are:


Nat Total Error :=
DIVIDE ( [Nat Total ALL F-A], [Total ALL A] )
-------------------------------------------------------------------------------
Nat Total ALL F-A :=
IF (
HASONEVALUE ( SCMWEEK[WEEK_IK] ),
CALCULATE (
[NatTotABSErr],
ALLSELECTED ( '1 WK Accuracy Output' ),
FILTER (
ALLSELECTED ( '1 WK Accuracy Output' ),
'1 WK Accuracy Output'[Week of Result]
= VALUES ( '1 WK Accuracy Output'[Week of Result] )
)
),
BLANK ()
)
-------------------------------------------------------------------------------
Total ALL A :=
IF (
HASONEVALUE ( SCMWEEK[WEEK_IK] ),
CALCULATE (
[SumTotDem],
ALLSELECTED ( '1 WK Accuracy Output' ),
FILTER (
ALLSELECTED ( '1 WK Accuracy Output' ),
'1 WK Accuracy Output'[Week of Result]
= VALUES ( '1 WK Accuracy Output'[Week of Result] )
)
),
BLANK ()
)
-------------------------------------------------------------------------------
NatTotABSErr :=
SUMX (
DISTINCT ( '1 WK Accuracy Output'[SKU_ID] ),
CALCULATE ( ABS ( [SumTotErr] ) )
)

Cheers

 

[Matt Allington]

It looks fine to me

how many columns in your longest table?
how many items are in your pivot table? I assume 6000, right? Why do you need to materialise 6000 records? Are there other options?
are you using 32 bit or 64 bit?

 

[monsoonnut]

Thanks Matt

I now have faith that my formulas are OK - maybe I'm better at this than I thought!

I have taken a different route and amended the report to work in a different way, lookups and VBA.

It now runs quickly and smoothly.