question

spivsteritis avatar image
spivsteritis asked

Analysis services cube calculated value for conditional count of measures

Hi, 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... Thanks
ssasmdxcubescalculationsanalysis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.