topic Re: Cumulative Total , no dates in DAX Commands and Tips

Cumulative Total , no dates

UrAvgWally — Tue, 16 Aug 2022 16:32:58 GMT

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 :)!

Re: Cumulative Total , no dates

Greg_Deckler — Tue, 16 Aug 2022 16:41:01 GMT

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

Re: Cumulative Total , no dates

UrAvgWally — Tue, 16 Aug 2022 17:06:24 GMT

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.

Re: Cumulative Total , no dates

Greg_Deckler — Tue, 16 Aug 2022 17:58:02 GMT

UrAvgWally

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

Re: Cumulative Total , no dates

UrAvgWally — Tue, 16 Aug 2022 18:14:35 GMT

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

Re: Cumulative Total , no dates

Greg_Deckler — Tue, 16 Aug 2022 18:25:54 GMT

UrAvgWally Ah, try this:

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

Re: Cumulative Total , no dates

UrAvgWally — Tue, 16 Aug 2022 21:53:53 GMT

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

Re: Cumulative Total , no dates

Greg_Deckler — Wed, 17 Aug 2022 13:11:57 GMT

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

Re: Cumulative Total , no dates

UrAvgWally — Wed, 17 Aug 2022 13:27:22 GMT

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?.

Re: Cumulative Total , no dates

Greg_Deckler — Wed, 17 Aug 2022 13:36:46 GMT

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)

Re: Cumulative Total , no dates

UrAvgWally — Wed, 17 Aug 2022 13:45:32 GMT

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.

Re: Cumulative Total , no dates

Greg_Deckler — Wed, 17 Aug 2022 14:25:59 GMT

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.

Re: Cumulative Total , no dates

UrAvgWally — Wed, 17 Aug 2022 14:47:06 GMT

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 :

Re: Cumulative Total , no dates

Greg_Deckler — Wed, 17 Aug 2022 18:24:04 GMT

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.