Power BI Running total horizontally

1
votes

I am working with Power BI tables and I am not being able to calculate balance "Running total" like I need.

I was searching in Stack Overflow and other webpages and I always find the same solution for a very similar situation, which is not this.

First of all, this is my table:

First table

I found in this and other sites the same solution:

Running Total COLUMN =
    CALCULATE (
        SUM ( 'My table'[Accounting Balance] ),
        ALL ( 'My table' ),
       'My table'[Date] <= EARLIER ( 'My table'[Date] ))

This would work whenever I need to sum the rows vertically, which is not my case. Indeed, I need to sum the valu horizontally:

Second table

Any suggestions?

Edit 1:

This is what I need:

Result table

So if you take a close look to this table it has the resulting calculation of each column for each vendor like:

  1. Vendor 1 owed $200 on January 2017
  2. Vendor 1 owed $0 on February 2017 because he made a $200 payment
  3. Vendor 1 owed $50 on March 2017 because $0 + $50
  4. Vendor 1 owed $50 on April 2017 because he didn't make any payment.
  5. Vendor 1 owes $50 in total.
  6. etc
3
excelpowerbidaxpowerquerym
Do you just want the end column to be a "Totals" column? Or did you want to add/transform rows so the value of each month is the running total of the original values? – Mistella
@Mistella, that is exactly what I need, calculate the running totals for each column for each vendor in each month. – Martin Fernandez
This is a bit of an X Y question. If at all possible, the best solution would be to unpivot your source table so that each date doesn't need its own column. I can explain how to do this, but wanted to ask if you're willing to do that first. – Alexis Olson
Alexis, thanks for answering, It's not up to me, I am getting the data from an OLAP cube on my company. – Martin Fernandez

3 Answers

2
votes

This is done in Power Query as I don't have BI, but I assume the M-Code will work just as well. It will also auto adjust if you refresh the query as you add/delete columns, so there is no need to reference each column individually.

You add a Custom Column which sums all the columns except the "Vendor/Month"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor/Month", type text}, {"Jan-17", Int64.Type}, {"Feb-17", Int64.Type}, {"Mar-17", Int64.Type}, {"Apr-17", Int64.Type}}),
    #"Sum" = Table.AddColumn(
    #"Changed Type",
      "Total",
  each List.Sum(
    Record.ToList(
      Record.SelectFields(
      _,
      List.RemoveItems(Table.ColumnNames(#"Changed Type"), {"Vendor/Month"})))))
in
    #"Sum"

This is the Custom Column Dialog:

enter image description here

And this is the result:

enter image description here

0
votes

I'm making a big assumption here: That "Totals" the column does NOT need a "Total" row.

If not, then isn't the Column defined as shown:

Totals = Table1[Jan-17]+Table1[Feb-17]+Table1[Mar-17]

PowerBI Totals Column

0
votes

As suggested earlier by Alexis Olson, you should import the 'table' you get from the OLAP-cube into the Power Query Editor and unpivot it. You would start with this:

enter image description here

Use a script like this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\927085\Documents\Marco\SO\SO18-10-1.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="myTable",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet3_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Totals"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Columns",1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Bottom Rows", {"Vendor/Month"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each "1-"&[Attribute]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Month"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Value", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Value", "Accounting Balance"}, {"Vendor/Month", "Vendor"}})
in
    #"Renamed Columns1"

And the resulting table would be this:

enter image description here

Then create the following maesure:

RunningTotal = 
CALCULATE (
    SUM ( myTable[Accounting Balance] ),
    FILTER (
        ALLEXCEPT ( 'myTable', myTable[Vendor] ),
        'myTable'[Month] <= SELECTEDVALUE ( myTable[Month] )
    )
)

In a matrix visual put Vendor on rows, Month on columns and the [RunningTotal] on Values.

enter image description here