How to calculate % over rows in power bi

Question

I have a PostgreSQL database where each row represents a day, and each column represents an attribute about the customers that been measured at the specific day. This database is being updated daily using python code. In these days I am trying to build a dashboard in Power Bi in order to share the data with stakeholders. I want to add to the dashboard a line chart which shows how one columns' values change overtimes. In this line chart, I want to show the change in percentage in each day. In excel it should look like this:

rthomsen rthomsen · Accepted Answer · 2019-08-30T21:33:30

You can accomplish this in Power Query (i.e. during data import and transformation) as follows:

Load the data, making sure the rows are ordered by date ascending:

Add an Index column "From 0", then another Index column "From 1":

Merge the table with itself, selecting "Index" first and "Index.1" second:

Expand "Column 1" from the new column added to your table:

Subtract the new column from the original value (select "Column 1" and "Added Index1.Column 1", then go to Add Column > Standard > Subtract):

Remove all unneeded columns:

Of course, you can then rename columns as necessary.

The Power Query code in this example is as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TczJCcAwDETRXnQ2aIsSqRbj/tuwCQqZ62P+zEkWnGyiRYNUhNY4doNF2wOWbQlWbfWbdesC1q0rWMVryvWRe98BXWe1Ng==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"Column 1" = _t]),  // set up the table as shown in your example
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Column 1", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Column 1"}, {"Added Index1.Column 1"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Expanded Added Index1", "Subtraction", each [Column 1] - [Added Index1.Column 1], Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"Index", "Index.1", "Added Index1.Column 1"})
in
    #"Removed Columns"

How to calculate % over rows in power bi

2 Answers