Cumulative Total , no dates

[UrAvgWally]

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]

@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]

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]

@UrAvgWally 

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

[UrAvgWally]

@Greg_Deckler   Thanks for getting back to me, unfortunately I am unfortunately still getting a syntax error !

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])

[Greg_Deckler]

@UrAvgWally Ah, try this:

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

[UrAvgWally]

@Greg_Deckler Hiya! Still not working 😞 .

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])

[Greg_Deckler · Accepted solution]

@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])

[UrAvgWally]

Hi @Greg_Deckler ,

The syntax error has been fixed, it gave an error saying Table is reserved so changed it to Table1, not sure if that is the right thing to do? But to add to that, it is still showing me on the graph, the sum by period rather than cumulative Sum in the graph not sure if it because I changed to Table1?.

[Greg_Deckler]

@UrAvgWally Right, one of the reasons I prefix my variables with __ to avoid conflicts like that. Tough to troubleshoot in forums without access to the PBIX or sample data to build the model myself. The theory behind the forumla is that you get the current period as variable Pno so PeriodicTable[PeriodNo] should be used in the X-Axis. You then use that to get all periods equal or less than that period. You can use CONCATENATEX to view the items in that table. Then you filter your unrelated table UnpivotCP for only the rows where the period is in your list of current and previous periods (Periods variable). Perhaps you have a relationship that is messing this up? If so, you could use ALL to get around this like:

VAR Table1 = FILTER(ALL(UnpivotCP),[PeriodMFMA] IN Periods)

[UrAvgWally]

@Greg_Deckler Awesome! The code fixed the issue and is now showing cumulative.

Although my slicer for different functions not working for that.I ideally want to be able to Slice by function , is there a way to enable that? As per above I use RSKCL[Function] as a slicer, which has a relation to the Funder column.

[Greg_Deckler]

@UrAvgWally You will want to use ALLEXCEPT instead of ALL in that case. Basically ignore all of the context except for the context you specify.

[UrAvgWally]

Alright, thanks . Thank you for being patient with me. Will start reading the book on Dax over the weekend to learn, it is arriving tommorow. 

The Allexcept is having syntax error :

[Greg_Deckler]

@UrAvgWally ALLEXCEPT takes additional parameters. So something like:

VAR Table1 = FILTER(ALLEXCEPT(UnpivotCP, 'UnpivotCP'[Function]),[PeriodMFMA] IN Periods)

So that code would ignore all context other than context created by a Function column in your UnpivotCP table for example.