CODEWITHSUNDEEP
excelssasmdx
Rathish Kumar B
Rathish Kumar B

Reputation: 1412

Dealing with repeating values in SSAS Cube and MDX

I am new to SSAS cubes and MDX, my fact table output is given below:

enter image description here

Background:

Each employee has a Capacity (8 hours) Each Employee assigned to Many Projects (A, B, C) An Employee Can be assigned to many projects on same day (Date)

Question:

When I calculate the total [AssignedHours] for an [Employee] for a week, I will do SUM([AssignedHours]), but when I do calculate the Total [Capacity] for the week, I am getting higher value, because the [Capacity] hours is repeating many times for a day based on the project. (I must get 40 hours - under normal circumstances).

I am sure, I am not the first person to face this scenario. Please let me know, how do I manage this scenario of avoiding repeating values.

Also, if it can be solved by MDX expression, how can make it as named calculation or any other method in the cube itself, so that when a new user connected to cube, no need to solve this again. Thank you.

Upvotes: 0

Views: 345

Answers (1)

MoazRub
MoazRub

Reputation: 2911

By looking at the description of your problem it looks like the capacity is part of your fact table. The capacity might be getting populated on each row of your fact table. If that is the case you can divide the Sum of capacity by the count of Rows. 

With member [Measures].[ActualCapacity] as [Measures].[Capacity]/[Measures].[RowCount]

Ideally you should have capacity as an attribute in your Cube.

Upvotes: 1

Related Questions