Analysis services cube calculated value for conditional count of measures


I'm struggling with trying to create a calculation in my cube. I have an accumulating snapshot fact table with some information including:

LocationKey (Foreign key to the location dimension) Process Identifier (unique key to identify the individual process grain) Process start date (Foreign key to date dimension) Process Days Overdue (A column calculated at ETL time for the number of days the individual process is overdue by)

When I build my cube, the measures I get are: Process Count (added automatically by SSAS for the count of all proceses) Process Days Overdue

What I'd like to be able to do is add a calculation for: Count of Overdue processes (i.e. number of processes where 'Days overdue' > 0) Count of On Target processes (i.e. number of processes where 'Days overdue' = 0)

I've had some limited success so far. I've managed to get the counts coming out correctly in relation to the total number of processes, but they fail to calculate correctly when I start slicing the data.

For example:

If I have 100 processes, 10 are overdue and 90 on target at the highest level in my cube I can get the information correctly. i.e.:

Total Count: 100 Overdue: 10 OnTarget: 90

However when I, for example split the data by location instead of getting:

Total Count (Location A): 80 Overdue (Location A) : 8 **OnTarget (Location A) :**72 Total Count (Location B) : 20 Overdue (Location B) : 2 OnTarget (Location B) : 18

my results are not splitting correctly and I'm getting: **Total Count(Location A):**80 **Overdue (Location A):**10 **OnTarget (Location A):**90 **Total Count (Location B) :**20 Overdue (Location B): 10 OnTarget (Location B): 90

Any help would be really appreciated. I'm not sure if I'm just doing something wrong, or if my whole understanding is incorrect and what I'm trying to do is impossible...


more ▼

asked Dec 05, 2012 at 11:39 AM in Default

avatar image

40 2 2 5

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 05, 2012 at 11:39 AM

Seen: 2194 times

Last Updated: Dec 05, 2012 at 11:39 AM

Copyright 2018 Redgate Software. Privacy Policy