Power BI Desktop - Running Totals - How to fill empty fields in Matrix Visualization

[powerbi-hvs]

Thank you for entering the Forum.

I want to calculate Running Totals for a Balance Sheet in a Pivot table using Measures and I want to calculate them right.

The AmountCum formula is as follows:

CALCULATE (
 SUM ( Table1[Amount] );
 FILTER ( ALL ( Table1 ); Table1[Year] <= MAX ( Table1[Year] ) );
 VALUES ( Table1[Ledger] ))

When there is no transaction the Running Total is not calculated and not summerized in the regarding Year.

As an example i have made this table with acquiring of a Building and tools in 2016 and additional tooling in 2017. Running totals left and transactions right.

The balance sheet of 2017 is not correct. 100 in Assets of the Building is missing because there is no transaction on de Building Ledger in that year. Also the Totals are not correct and the Balance is not in balance...

I have searched for a lot of hours and therefor I make this post. Who can help me to fix the Balance Sheet for 2017?

Please explain to me this is not a bug...

B	A	Building	2016	100
B	L	Equity	2016	-100
B	A	Tools	2017	10
B	L	Equity	2017	-10
B	A	Tools	2016	50
B	L	Equity	2016	-50

[javiguillen · Accepted solution]

Powerbi-hvs,

The following formula would work, with one exception that explain below:

Running Total =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER ( ALL ( 'Table'[Year] ), 'Table'[Year] <= MAX ( 'Table'[Year] ) )
)

That would work for all values except for "Building, 2017" where there is no data at all in the table.  Any DAX calculation can work where there is data, but if there is no data at all the result is blank.  To show the 100 as you need, you would have to insert a value for Building, 2017.  Here is where DAX tables can come in handy.  You can define one DAX table as:

Table =
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( Table1[Year] ),
        VALUES ( Table1[AP] ),
        VALUES ( Table1[BR] ),
        VALUES ( Table1[Ledger] )
    ),
    "Amount", CALCULATE ( SUM ( Table1[Amount] ) )
)

Which will create a row for each combination of Year, AP, BR, and Ledger values, but only assign an amount when there is one.  You can then execute the running total as defined above, and the result will be exactly what you need.

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[powerbi-hvs]

Dear @Ashish_Mathur

Thank you a lot for your PBIX. Your model does show the figures as requested in my post.

However, if I add another transaction of 10 for Tools in 2018, the same gap will come back.

This is because your formula of the Value with Datesbetween and Edate is exactly looking 12 months back.

In essence the empty years are only missing a record with a zero value.

So far I estimate the answer of @javiguillen as the most appropriate definitive solution for filling empty fields in Matrix Visualizations.

To which extend do you share my view on this?

Regards, HvS

[v-caliao-msft]

@powerbi-hvs,

We cannot understanding your issue based on your description. Could you please elaborate your issue in details, so that we can make further analysis.

Here are some userful links about running total in DAX for you reference.
http://www.daxpatterns.com/cumulative-total/

https://javierguillen.wordpress.com/2012/11/28/running-total-techniques-in-dax/

Regards,

Charlie Liao

[powerbi-hvs]

@@v-caliao-msft

Thank you for your quick reply and question for explanation.

I do know the information in the two links you have sent. These links do not address the issue.

A running total should show the t-1 value in case there is no new value in the actual period.

 

In case of a real Running Total the Matrix should present the following figures:

The Building 100 from 2016 should be presented again in 2017 and the total of the Balance Sheet in 2017 has to be Zero.

Het function Running Total in Excel does indeed give the right repeated figures, even if there is no transaction to label the calculation (or Measurement in Power BI) to.

 

There are two options to solve this:

1. Programming DAX in adding additional records with zero values for missing transactions in reporting periods
2. Adapt the Visualization called Matrix in Power BI so this will also be able to show Running Totals instead of trying to solve this in the data, where it is extra complicated to solve it

The second option is posted many times as an Idea and is also under Review as an Improvement since June 2015.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7928763-how-about-some-power-quer...

Please help to solve the issue of a missing values in a measure of the Running Total in or upgrade the Idea of Running Totals to Essential.

Charlie, have I explained enough to fully understand the issue?

[javiguillen · Accepted solution]

Powerbi-hvs,

The following formula would work, with one exception that explain below:

Running Total =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER ( ALL ( 'Table'[Year] ), 'Table'[Year] <= MAX ( 'Table'[Year] ) )
)

That would work for all values except for "Building, 2017" where there is no data at all in the table.  Any DAX calculation can work where there is data, but if there is no data at all the result is blank.  To show the 100 as you need, you would have to insert a value for Building, 2017.  Here is where DAX tables can come in handy.  You can define one DAX table as:

Table =
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( Table1[Year] ),
        VALUES ( Table1[AP] ),
        VALUES ( Table1[BR] ),
        VALUES ( Table1[Ledger] )
    ),
    "Amount", CALCULATE ( SUM ( Table1[Amount] ) )
)

Which will create a row for each combination of Year, AP, BR, and Ledger values, but only assign an amount when there is one.  You can then execute the running total as defined above, and the result will be exactly what you need.

[powerbi-hvs · Accepted solution]

Dear @javiguillen,

Thank you a lot, to my knowledge you have provided the right solution and practical DAX to make it work.

A minor detail: because AP and BR are redundant to Ledger, the table with all the necessary records, is two lines shorter to limit the number of added records to the existing Years for each Ledger:

Table =
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( Table1[Year] ),
        VALUES ( Table1[Ledger] )
    ),
    "Amount", CALCULATE ( SUM ( Table1[Amount] ) )
)

Thanks to all who have contributed!

@javiguillen

@Ashish_Mathur

@v-caliao-msft

Marco Russo

NB, Marc Russo has shown there is an option using a date table for better performance. I see the advantages and will investigate, to be continued.

Warm Regards, HvS

[powerbi-hvs]

@v-caliao-msft

Thank you for your quick reply and question for explanation.

I do know the information in the two links you have sent. These links do not address the issue.

A running total should show the t-1 value in case there is no new value in the actual period.

In case of a real Running Total the Matrix should present the following figures:

The Building 100 from 2016 should be presented again in 2017 and the total of the Balance Sheet in 2017 has to be Zero. The above result is presented by fysically adding transactions with value 0 in 2017 for Buildings. This is not a favorable option in cases with millions of transactions.

Het function Running Total in Excel does indeed give the right repeated figures, even if there is no transaction to label the calculation (or Measurement in Power BI) to.

 

There are two options to solve this:

1. Programming DAX in adding additional records with zero values for missing transactions in reporting periods

2. Adapt the Visualization called Matrix in Power BI so this will also be able to show Running Totals instead of trying to solve this in the data, where it is extra complicated to solve it

In May 2015 the second option is posted many times as an Idea and is also under Review as an Improvement since June 2015.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7928763-how-about-some-power-quer...

Please help to solve the issue of a missing values in a measure of the Running Total in or upgrade the Idea of Running Totals to Essential.

Charlie, have I explained enough to fully understand the issue?