Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculation using a Calculated Measure Expand / Collapse
Author
Message
Posted Friday, September 12, 2008 9:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
I have a calculated measure that is based on two other measures. It is pretty close to this:

CASE
WHEN [Measures].[SkipUnits] > [Measures].[Units] THEN null
ELSE [Measures].[Units] - [Measures].[SkipUnits]
END


Obviously, I need to reduce my units by my skipped units but the units calculation cannot be less than zero.
This is all dandy until this measure is used by a calculation showing the variance between this year and last year (or any other variance). In these cases, I want the calculated measure to calculate first and then the variance calculated because it is ok to have a negative variance.

Anyone have a way of doing this that does not involve specifying the calculation for this measure manually for each of the variance calculations? Unfortunately, I have a lot of them.
Post #568682
Posted Friday, September 12, 2008 12:56 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Today @ 12:54 PM
Points: 1,822, Visits: 3,484
You should be able to specify the solve order. I think it was the higher the number the later in the calc pass it would resolve (ie set the calc based on calc to be a number > first calc). It's a little dated now but here's a blog entry on creating calc members, you could also check out the info on msdn re: calc scripts.


Steve.
Post #568868
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse