Forum Discussion

mtmcd's avatar
mtmcd
Frequent Visitor
8 years ago
Solved

Cumulative Line and Clustered Column Chart

I'm having a difficult time plotting a cumulative (running total) line on top of a clustered column chart for goal vs actual quantities.

 

Here is my raw data, which shows Goal vs Actual quantities for a given process, and product category and model:

 

 

 

 

 

 

 

I then reshape the data to look like this:

 

Finally, I create my running total column, which does not add the values appropriately:

RunningTotalActual = CALCULATE(SUM('Overall Summary - Formatted'[Quantity]),FILTER(ALL('Overall Summary - Formatted'[Date]),'Overall Summary - Formatted'[Date] <= MAX('Overall Summary - Formatted'[Date])),FILTER(ALL('Overall Summary - Formatted'[Status]),'Overall Summary - Formatted'[Status] = "Actual"),FILTER(ALL('Overall Summary - Formatted'[Process]), 'Overall Summary - Formatted'[Process] = "Wax"))

The cumulative line chart simply matches what the actual values are instead of adding them as time progresses:

 

Is there an error in my code or the way I have my data set up?

Need Help
  • BILASolution's avatar
    BILASolution
    8 years ago

    Hi mtmcd

     

    1. If the results You're looking for is the next picture...

     

     

     

    try this measures...

     

    Accumulative Actual = var actualdate = FIRSTNONBLANK(Process[Date];1) return CALCULATE([Total Quantity];ALL(Process[Date].[Day]);Process[Status] = "Actual";Process[Date] <= actualdate) 
    Accumulative Goal = var actualdate = FIRSTNONBLANK(Process[Date];1) return CALCULATE([Total Quantity];ALL(Process[Date].[Day]);Process[Status] = "Goal";Process[Date] <= actualdate)

    Regards

    BILASolution

     

3 Replies

  • BILASolution's avatar
    BILASolution
    Solution Specialist
    8 years ago

    Hi mtmcd

     

    Here is the solution...

     

    1. Sample Data: My table is called "Process"

     

     

     

    2. Measures:

     

    Total Quantity = SUM(Process[Quantity]) 
    Total Actual = CALCULATE([Total Quantity];ALL(Process[Date].[Day]);Process[Status] = "Actual") 
    Total Goal = CALCULATE([Total Quantity];ALL(Process[Date].[Day]);Process[Status] = "Goal")

    3. Final View

     

     

     

    Regards

    BILASolution

    • mtmcd's avatar
      mtmcd
      Frequent Visitor
      8 years ago

      BILASolution,  thank you for taking the time to propose a solution.  However, it does not appear that the "Total Actual" and "Total Goal" lines in your chart are accurate.  They are not plotting the running total of the values over time; they are all a steady amount.

       

      As for my particular application, the Actual and Goal measures are coming up blank using your measures...

       

      Wouldn't I need to implement a new column to calculate running total instead of a measure?

      • BILASolution's avatar
        BILASolution
        Solution Specialist
        8 years ago

        Hi mtmcd

         

        1. If the results You're looking for is the next picture...

         

         

         

        try this measures...

         

        Accumulative Actual = var actualdate = FIRSTNONBLANK(Process[Date];1) return CALCULATE([Total Quantity];ALL(Process[Date].[Day]);Process[Status] = "Actual";Process[Date] <= actualdate) 
        Accumulative Goal = var actualdate = FIRSTNONBLANK(Process[Date];1) return CALCULATE([Total Quantity];ALL(Process[Date].[Day]);Process[Status] = "Goal";Process[Date] <= actualdate)

        Regards

        BILASolution