Calculating percentage using aggregate data? RRS feed

  • 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?



    • Edited by rbhatup 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.

    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 Butmah Tuesday, April 29, 2014 5:43 AM
    • Marked as answer by Charlie LiaoModerator Wednesday, May 7, 2014 2:20 AM
    Tuesday, April 29, 2014 1:51 AM