# Calculating percentage using aggregate data?

• ### Question

• I'm new to SSAS/BI, but the data I'm working with is a great opportunity to get some experience with a data warehouse and being able to build my first cube. So please bear with me if the question doesn't make sense.

I have a standard DimTable and a Fact Table that looks like this:

Date          Hour     Failures     Attempts
20140201   14       5            10
20140201   15       6            10
20140201   16       2            8
20140201   17       4            8
20140201   18       7            28
20140201   19       2            8
20140201   20       7            10
20140201   21       6            20
20140201   22       1            5

Let's say I want to calculate the fail rate percentage on a daily/weekly/monthly basis. The cube would include the aggregates of failures and attempts in N period, but is it possible that the cube include these percentages?

So let's say I want to calculate the fail rate with the data above, then the percentage would be 40/107 or .37.

Is it somehow possible to do this with the cube?

Thanks.

VM

• Edited by Monday, April 28, 2014 9:22 PM
Monday, April 28, 2014 9:21 PM

• Hi VM,

In order to handle this you will have to create a calculated member named [fail rate percentage].

Before doing that, from the above mentioned fact table, you will have to create 2 measures Sum(Failures) and Sum(Attempts), which I suppose you already have.

Using these measures create a calculated member [Fail Rate Percentage]

Expression : [Measures].[Failures]/[Measures].[Attempts]

Format String : "Percent"

Below link would give insights on how to create calculated member.

http://technet.microsoft.com/en-us/library/ms166568.aspx

Remember calculated members are not stored physical in the cubes, they are calculated on the fly while querying the cube. And that is suffice in your case.

Saurabh Kamath

• Proposed as answer by Tuesday, April 29, 2014 5:43 AM
• Marked as answer by Wednesday, May 7, 2014 2:20 AM
Tuesday, April 29, 2014 1:51 AM