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

Calculated Measures using Case or IIF statement and not getting correct Subtotals on Pivot tables. Expand / Collapse
Author
Message
Posted Wednesday, May 29, 2013 4:08 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 671, Visits: 198
I have the following Calculated measure -

CREATE MEMBER CURRENTCUBE.[Measures].[Act - Fcst Lbs] AS

case when [Measures].[Act- Fcst Flag]= 0
then [Measures].[Invoiced Pounds]
else [Measures].[DMP - Forecast Lbs]
end,
FORMAT_STRING = "$#,##0;($#,##0)",
VISIBLE = 1 , DISPLAY_FOLDER = 'Calcs' , ASSOCIATED_MEASURE_GROUP = 'DMP Forecast';


works great. however when I display with our calendar hierarchy Dimension, the months will display the correct values, but the subtotal on Year will not be correct. I know its because the calculation is occurring on the Year line. I've tried a Scope statement to override the year, which works IF you select all the months in the year. but if you select just a couple, then the total is not correct.

If the source if the values were not in 2 different places I'd take care of this in the SQL source table.

Our Calendar Dimension Hierarchy is as follows

[Calendar].[Fiscal],[Fiscal Year].[Fiscal Month]

Any directed would be grateful
Post #1457985
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse