Cumulative/Running Balance in a Matrix

[shaunwilks]

Hello All,

Would appreciate any assistance on this if possible.

I would like to know who to write a DAX measure that gets the last of the cumulative figures for a period, but then totals all those cumulative figures in the Matrix control.

I have my fact table and that fact table includes a running total whgich works beautifully.

DATE..........CODE.............VALUE...............RUNNING TOTAL

01/01..................A.................50.................................50

01/02..................B.................150..............................150

01/02..................B.................50.................................200

01/03..................A.................150..............................200

01/04..................B.................50.................................250

30/05..................A.................150..............................400

30/05..................A.................50.................................400

* The running total for the 30/05 is the same as its a running total as per date, and there are no times in the fact tables.

When I put this in a matrix is where I get the problem. I want to display only the last RUNNING TOTAL for each period. (Ie that shows the total YTD of that value as at the end of the month)

I have tried all sorts of combinations of this....

CALCULATE(FIRSTNONBLANK(Table1[Running Total],TRUE()), FILTER(GenLedger,Table1[Date]=Max(Table1[Date]))) 

Results 

The correct result is provided at the lwest level of the matrix.

But when the column is summarised before drill down - the SubTotal in the matrix is not the sum of the lines underneath, its the first non blank row.

CALCULATE(AVG(Table1[Running Total]), FILTER(GenLedger,Table1[Date]=Max(Table1[Date]))) 

Results 

All the running totals for the date are the same so it stands to reason that at the lowest line level the value is displayed correctly - howevre in the SUbotal for the line it again takes the average of all codes underneath and becomes incorrect.

Essentially what I am trying to so is have the summarisation subtotals in the matrix sum the values underneath, but the measure that displays at the line level I want to be the avg, min or max functions.

Unfortunately I cant get it workins as whatever function is applied in the measure is applied also in the matrix subtotal

Any guidance would be appreciated. Thanks

[Seward12533]

Use ISFILTERED in an IF statement to specify an alternate calculation for the summary row.

IF(ISFILTERED(date[date]),[RunningTotalMeas],[AltMeasure])

Not the same but see this example. https://1drv.ms/u/s!AuCIkLeqFmlhhJoSaNg6AMjIQC8Sgw

[shaunwilks]

I have that sample from antoher thread you were happy to try and assist me with.

I am still batlling with both unfortunately.

In this case I have no doubt that the ISFILTERED function is the one I need to use.

The problem I have is what measures I put in the ValueIfTrue and ValueIfFalse.

I have a measure that shows correctly when the measure is drilled down.

So I would imagine thats what you refer to as the RunningTotalMeas below.

The second measure is the battle. I want to sum the RunningTotalMeas itself.

So the lines are to show the Last value in that time period and the Header is to show the sum of the lines.

Whatever I do I end up getting the Last value both at the line level and the header level of the group.

[Seward12533]

You would use SUMMARIZE to build a virtual table with the running total column and then sum that.

I don’t have an example handy for that. But try and it you get stuck post a reply or new question.

[shaunwilks · Accepted solution]

Thanks for that - I have never used SUMMARIZE before so will do some work and investigation into it.

I think I found a wokraround for my problem in the header.

I added an index to the table thats sorted by date.

I then added a column that retrieved the MAX Index for rows in the table with the the same period (ie month).

That means I have the index number of the very last record in the month for the fact table and primary key.

Another column is a simple IF statement that sets the value of the field to the running balance value IF the index of that record is the last index id of rows in the same period else set 0.00.

I therefore end with the running balance, set only in the last record on the month.

When its dropped into a matrix it sums up perfectly.

I think I may now do the same for the QTR, and use your ISFILTERED Suggestion to show the QTR value if filtered by qtr and month if filtered by month.

Thanks for all your assistance,