none
Cumulative line graph RRS feed

  • Question

  • How does one create a cumulative line graph in PerformancePoint Services?

    If on Day 1 I have a count of 5, Day 2 is 10, Day 3 is 8, then Day 4 should be 23... and so on. Is this achievable?

     

    Sincerely,
    DT

    Thursday, January 6, 2011 4:32 PM

Answers

  • This could be achieved with a calculated member for the particular measure.  Something like the following:

    WITH MEMBER [Measures].[CumulativeCount] AS SUM({NULL:[Date].[Date].CurrentMember},Measures.[Count])

    So Date would be included in the query along with the calculated member to get at the running total.

    Check out this posting here in the SSAS forums - Mdx Running Total

     


    Dan English's BI Blog
    Thursday, January 6, 2011 8:27 PM

All replies

  • This could be achieved with a calculated member for the particular measure.  Something like the following:

    WITH MEMBER [Measures].[CumulativeCount] AS SUM({NULL:[Date].[Date].CurrentMember},Measures.[Count])

    So Date would be included in the query along with the calculated member to get at the running total.

    Check out this posting here in the SSAS forums - Mdx Running Total

     


    Dan English's BI Blog
    Thursday, January 6, 2011 8:27 PM
  • Hi Dan

    I don't understand...
    " WITH MEMBER [Measures].[CumulativeCount] AS SUM({NULL:[Date].[Date].CurrentMember},Measures.[Count]) "

    as well as the contents in MDS Running Total. If there a tutorial for this with data to practice on?

    Thanks!
    DT

    Thursday, January 13, 2011 3:19 PM
  • Do you have an environment setup already? If no you can use AdventureWorks sample datawarehouse available at:

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

    Choose the one matching your environment in terms of SQL version.

    What Dan suggested is the syntax to be used in your Analysis Services solution to accomplish running total through Calculated Measure.

    You may be able to use the same syntax in Dashboard Designer's Analytical Report's query tab.


    http://dailyitsolutions.blogspot.com/
    Friday, January 14, 2011 7:52 AM
  • One thing you will need to watch out for this that the example calculation provided will be the running value from the beginning of time [All].  If you just wanted to look at the YTD running total you could do something like the following:

    SUM( YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])

    This example is using the Calendar user-defined hierarchy as well.  You can put this calculation in the cube or in the Query section of your Analytical Report (just remember you will lose interactivity if you do this - meaning if you manually edit the Query in Analytical Report).


    Dan English's BI Blog
    • Edited by Dan English Saturday, January 22, 2011 12:09 PM remove sample with existing, didn't work as expected. added YTD example
    Tuesday, January 18, 2011 12:59 PM