Forum Discussion

vonckk's avatar
vonckk
Frequent Visitor
5 years ago
Solved

Creating Cumulative Measure referencing another meausre

Hello,


I could really use some help with a visual I'm working on.  I'm looking to show the actual spend, forecasted spend, monthly delta, and the cumulative delta throughout the year.  The chart below has all of the data elements except for the cumulative total.

 

 

The actual value and forecast value are a part of my base data set.  I've created a measure to track the absolute delta.  I used the below formula:

 

R&A Absolute Delta = abs(sum('Import - RA Financials'[Actual Value])-sum('Import - RA Financials'[Forecast Value]))
 
This seems to work pretty well as it is aggregating the data by months and displaying it correctly.  I'm having issues creating the cumulative total.  I was able to create one when referencing a specific column, but when I try to reference the Absolute Delta measure, it gives me an error.  Does anyone know if it's possible to reference this measure and get a cumulative total from it?
 
Also for bonus points, any idea on the best way to only measure the delta on months that have actual and forecasted spend?
 
Thanks for the help!

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi vonckk ,

     

    Try using SUMX() function.

    Measure = ABS(SUM('Table'[value1])-SUM('Table'[value2]))

Measure 2 = SUMX(FILTER(ALLSELECTED('Table'),'Table'[date]<=MAX('Table'[date])),[Measure])

     

    Best Regards,

    Jay

3 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    5 years ago
    can you provide the code you used for the cummulative total? What error did you get.
    • vonckk's avatar
      vonckk
      Frequent Visitor
      5 years ago

       

      Cumulative R&A Project Delta =
      Calculate(
      [R&A Absolute Delta],
      Filter(
      Allselected('Import - RA Financials'[R&A Period Date]),'Import - RA Financials'[R&A Period Date]<=max('Import - RA Financials'[R&A Period Date])))
       
      This is actually not giving me an error, but is just duplicating the same line as in the original graph.  I'm not seeing any cumulation happening by date.

      Thanks,
       
      Kyle
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi vonckk ,

     

    Try using SUMX() function.

    Measure = ABS(SUM('Table'[value1])-SUM('Table'[value2]))

Measure 2 = SUMX(FILTER(ALLSELECTED('Table'),'Table'[date]<=MAX('Table'[date])),[Measure])

     

    Best Regards,

    Jay