Forum Discussion

UrAvgWally's avatar
UrAvgWally
Icon for Helper I rankHelper I
4 years ago
Solved

Cumulative Total , no dates

Hi all,

 

I am trying to turn this line chart into a cumulative chart : 

 

 

The data Source looks like this , the X axis is the period Column

 

The Y Axis is based on these values :

Is it possible to cumulative add the Totals so my line chart can show case the cumulative totals rather than value for each period?

 

Also note, I have a slicer which looks at funder and see which department it belongs to. And so the graph changes to data for each department .

Any help would be greatly appreciated :)!

  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    UrAvgWally Yeah, there is a stray ) in there:

    Measure = 
VAR PNo = MAX(PeriodicTable[PeriodNo])
VAR Periods = 
  SELECTCOLUMNS(
    FILTER(
      ALL(PeriodicTable), 
      [PeriodNo] <= PNo
    ),
    "__PeriodMFMA",
    PeriodicTable[PeriodMFMA]
  )
VAR Table = FILTER(UnpivotCP,[PeriodMFMA] IN Periods)
RETURN
SUMX( Table,[Value])

13 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    UrAvgWally Hard to be specific since the data was incomplete without the department and posted as images versus copyable text. But, in general you are going to need to break context in your measure like:

    Measure =
  VAR __PNo = MAX('XTable'[PeriodNo])
  VAR __Periods = SELECTCOLUMNS(FILTER(ALL('XTable'),[PeriodNo] <= __PNo,"__PeriodMFMA",[PeriodMFMA])
  VAR __Table = FILTER('YTable',[PeriodMFMA] IN __Periods)
RETURN
  SUMX(__Table,[Value])
    • UrAvgWally's avatar
      UrAvgWally
      Icon for Helper I rankHelper I
      4 years ago

      Hi Greg_Deckler , thanks for getting back to me!

       

      Here is more snippets of data which I didnt show earlier :

      So the established relationships are  between UnpivotCP[PeriodMFMA] and PeriodTable[PeriodMFMA] and RSKCL[Funder] and UnpivotCP[Funder].

       

      With Hopes to use "Period" in Y axis instead of PeriodMFMA and Use RSKCL[Function] as Slicer due to the relationships with Funder.

       

      Hope that makes sense, any elaborations , please let me know.

       

      I have tried the below measure from what you sent, but I am guessing I made some mistakes typing it..

       

      Measure =
      VAR PNo = MAX(PeriodicTable[PeriodNo])
      VAR Periods = SELECTCOLUMNS(FILTER(ALL(PeriodicTable[PeriodNo] <= PNo,PeriodicTable[PeriodMFMA])
      VAR Table = FILTER(UnpivotCP,[PeriodMFMA] IN Periods)
      RETURN
      SUMX( Table,[Value])
       
      Thank you for your help again. 
      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        4 years ago

        UrAvgWally 

        VAR Periods = SELECTCOLUMNS(FILTER(ALL(PeriodicTable[PeriodNo]) <= PNo),"__PeriodMFMA",PeriodicTable[PeriodMFMA])