locked
how to calculate total no. of rows RRS feed

  • Question

  • Hi All,

     I have a measure which returns 1 if the condition is met else 0 .

     I want to count the total no. of rows having 1's and divide it by no. of days in a month. I am not getting a way to calculate the total count for the rows having values 1 I tried with count, filter, sum functions but everythng failed as the result is returned is cells wise and not the total.

     eg.

              forecasted calls       actual calls         Accuracy           accuracy_Percent

                 3029                    2673                  0                       0%

                5507                     5458                  1                       100% 

                4287                     4737                  1                       100%

     Total   12823                  12868                  2                       66.67% (2 divide by 3 percent)

    all totals are taken care except for accuracy and accuracy_Percent Also , I am writing these calculations on cube in SSAS 2008, where I am not able to use "select and with "

     Any help on this will be highly appreciated.

     Thanks,

    C


    Wednesday, April 27, 2011 11:50 AM

Answers

  • ok,

    descendants expect as second parameter a level expression, so you have to modify your formula in

    Sum
      (
        Descendants
        (
        [Date].[Date].CurrentMember
         ,[Date].[Date].[Date]
        )
       ,IIF
        (
           [Measures].[ Accuracy] =1
         ,1
         ,null
        ))
    / Count
      (Descendants
        (
        [Date].[Date].CurrentMember,
         [Date].[Date].[Date]
         ) )

    bye


    Norman
    • Marked as answer by Chaitali Prabhu Friday, April 29, 2011 6:32 AM
    • Unmarked as answer by Chaitali Prabhu Friday, April 29, 2011 6:32 AM
    • Marked as answer by Challen Fu Thursday, May 26, 2011 11:04 AM
    Thursday, April 28, 2011 1:13 PM

All replies

  • Hi,

     

      I am not sure whether this might prove correct for you. You can try out using the Sum and iif expression to get the count of accuracy where the value equals 1.

     

    Sum(iif([Measures].[Accuracy]=1,1,Null))

     

    HTH,

    Ram

    Wednesday, April 27, 2011 5:29 PM
  • Hello Ram,

    Thanks for your reply, but this did not help me. It's throwing calculation error.

    I tried the following query :-

    case [Measures].[Staffing Forecast Accuracy-Sales]
    when 1 then  sum ([Measures].[Staffing Forecast Accuracy-Sales])/[Measures].[Total Calls Staff]
    end

    but what it does is , it takes the accuracy value 1 and divides it by no of days for that accuracy value i.e. 1

    and for total it takes accuracy value 1 and divides it by no. of days in the month i.e. 31 for Jan.

    Please let me know if ne other function can work for this.

    Thanks

    C

    Thursday, April 28, 2011 5:18 AM
  • Any idea on this query?
    Thursday, April 28, 2011 9:46 AM
  • Hi , 

    try with descendants

    Sum

      (
        Descendants
        (
          [TIME DIM].[Time Hrc].CurrentMember
         ,[TIME DIM].[Time Hrc].[Day]
        )
       ,IIF
        (
            [Accuracy]=1
         ,1
         ,null
        )
      )

      Count
      (
        Descendants
        (
          [TIME].[Time Hrc].CurrentMember
         ,[TIME].[Time Hrc].[Day]
        )
      )

     

     this will count number of days with accuracy =1 and divide it by number of days.

    Does it fit?

     

    bye


    Norman
    Thursday, April 28, 2011 10:08 AM
  • Hey Norman,

    the output that i get after trying your query looks something like below:-

     Month         Date                      forecasted calls       actual calls         Accuracy           accuracy_Percent

     January      January 1 2011           3029                    2673                0                     99.73%

     January      January 2 2011           5507                    5458                1                     99.73%

     January      January 3 2011           4287                    4737                1                     99.73%

                     Total                        12823                  12868                 2                     99.73%

    :(

    I am trying to create a calculated member in ssas. what is most important is the value in the Total row. which is supposed to be (2/3) in the above example.

     

    Thursday, April 28, 2011 11:03 AM
  • Hi,

    to better understand what is happening I would suggest you to "debug" the formula..

    which is the result if you put only the numerator in the formula? does it count days with accuracy=1?

    which is the result if you put only the denominator in the formula? does it count the nb of days?

     

    If you could, please send me the query and the calculated member text.

     

    Thanks

     


    Norman
    Thursday, April 28, 2011 12:22 PM
  • hi Norman,

    The issues is

    Month         Date                      forecasted calls       actual calls         Accuracy           accuracy_Percent

     January      January 1 2011           3029                    2673                0                     99.73%

     January      January 2 2011           5507                    5458                1                     99.73%

     January      January 3 2011           4287                    4737                1                     99.73%

                     Total                        12823                  12868                 2                     99.73%

     

    Total (for forecasted calls and actual call) is calculated automatically by the system . i tried debugging the way you said, it does not take the total for Accuracy and accuracy percent.

    Below is the query I tried under a new calculated member called [Count_Percent]:-

    Sum
      (
        Descendants
        (
        [Date].[Date].CurrentMember
         ,[Date].[Date]
        )
       ,IIF
        (
           [Measures].[ Accuracy] =1
         ,1
         ,null
        ))
    / Count
      (Descendants
        (
        [Date].[Date].CurrentMember,
         [Date].[Date]
         ) )

    which gives me output as :-

    Month         Date                      forecasted calls       actual calls         Accuracy           accuracy_Percent

     January      January 1 2011           3029                    2673                0                     

     January      January 2 2011           5507                    5458                1                     100%

     January      January 3 2011           4287                    4737                1                     100%

     January     Total                        12823                  12868                 1                     100%

     

    The logic is :-  i need to count for how many days in a month was the accuracy = 1 / total no. of days in a month

    Thursday, April 28, 2011 12:46 PM
  • ok,

    descendants expect as second parameter a level expression, so you have to modify your formula in

    Sum
      (
        Descendants
        (
        [Date].[Date].CurrentMember
         ,[Date].[Date].[Date]
        )
       ,IIF
        (
           [Measures].[ Accuracy] =1
         ,1
         ,null
        ))
    / Count
      (Descendants
        (
        [Date].[Date].CurrentMember,
         [Date].[Date].[Date]
         ) )

    bye


    Norman
    • Marked as answer by Chaitali Prabhu Friday, April 29, 2011 6:32 AM
    • Unmarked as answer by Chaitali Prabhu Friday, April 29, 2011 6:32 AM
    • Marked as answer by Challen Fu Thursday, May 26, 2011 11:04 AM
    Thursday, April 28, 2011 1:13 PM