Cumulative chart based on sorted column

Hello,

I am a beginner in PowerBI and I need to visualize cumulative quantities based on sorted price column. I need to show a chart where on Y axis I will have the price and on the X axis I will have the cumulative total amount calculated based on the sorted price column (descending). Please see below.

Could you please help?

Thanks a lot

Need Help

PhilipTreacy

5 years ago

Hi puchrova

The cumulative column is created in Power Query. It will automatically update when new data is loaded.

You need to open the PBIX file I linked to and then click on Transform Data. This will open the query editor and you can then open the Advanced Editor to see the M code that makes up the query.

Because you only provided an image as sample data I entered this by hand to create the example, but you will need to create your own query that loads you data - presumably from an Excel workbook?

When you open the Advanced Editor in my PBIX file you'll see this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTIyUIrViVYyNACyDSFsYyDTBMI0AgubKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [price = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"price", Int64.Type}, {"amount", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"price", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Cumulative Total", each List.Sum(List.FirstN(#"Added Index"[amount], [Index]))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Cumulative Total", Int64.Type}})
in
    #"Changed Type1"

You need to replace the Source step with a couple of steps that loads your data. If your data comes from a workbook the new query will look something like this

let
    Source = Excel.Workbook(File.Contents("D:\temp\data.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"price", Int64.Type}, {"amount", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"price", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Cumulative Total", each List.Sum(List.FirstN(#"Added Index"[amount], [Index]))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Cumulative Total", Int64.Type}})
in
    #"Changed Type1"

So in this new query th data isloaded from the file located at d:\temp\data.xlsx and you need to change that path/name to suit your file, and the data is in an Excel table called Table1

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

7 Replies

PhilipTreacy
Super User
5 years ago
Hi puchrova
Please download this example PBIX file with the data and visual below.
After loading the data, you can create acumulative total in Power Query. There are many ways to do this, this is just 1 approach.

You can then insert a line chart plotting Cumulative Total v Price.
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
- puchrova
  New Member
  5 years ago
  Thank you, but how did you create the cumulative column? I will need it to update automatically every day with new data for that day.
  - PhilipTreacy
    Super User
    5 years ago
    Hi puchrova
    The cumulative column is created in Power Query. It will automatically update when new data is loaded.
    You need to open the PBIX file I linked to and then click on Transform Data. This will open the query editor and you can then open the Advanced Editor to see the M code that makes up the query.
    Because you only provided an image as sample data I entered this by hand to create the example, but you will need to create your own query that loads you data - presumably from an Excel workbook?
    When you open the Advanced Editor in my PBIX file you'll see this code
    
    let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTIyUIrViVYyNACyDSFsYyDTBMI0AgubKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [price = _t, amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"price", Int64.Type}, {"amount", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"price", Order.Descending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Cumulative Total", each List.Sum(List.FirstN(#"Added Index"[amount], [Index]))), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Cumulative Total", Int64.Type}}) in #"Changed Type1"
    
    You need to replace the Source step with a couple of steps that loads your data. If your data comes from a workbook the new query will look something like this
    
    let Source = Excel.Workbook(File.Contents("D:\temp\data.xlsx"), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"price", Int64.Type}, {"amount", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"price", Order.Descending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Cumulative Total", each List.Sum(List.FirstN(#"Added Index"[amount], [Index]))), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Cumulative Total", Int64.Type}}) in #"Changed Type1"
    
    So in this new query th data isloaded from the file located at d:\temp\data.xlsx and you need to change that path/name to suit your file, and the data is in an Excel table called Table1
    Regards
    Phil
    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
mahoneypat
Microsoft Employee
5 years ago
Please try this measure expression in your visual. Replace Price with your actual table name.

NewMeasure =
VAR vThisPrice =
    MIN ( 'Price'[Price] )
RETURN
    CALCULATE (
        SUM ( 'Price'[Amount] ),
        ALL ( 'Price'[Price] ),
        'Price'[Price] >= vThisPrice
    )

Regards,

Pat
- puchrova
  New Member
  5 years ago
  Thanks but I'm not sure how to show it on the visual, it won't let me put the new measure with cummulative total on the x axis?
amitchandak
Super User
5 years ago
puchrova , Try a measure like

Cumm = CALCULATE(SUM(Table[Amount]),filter(allselected(Table),Table[Amount] <=max(Table[Amount])))

but which visual you are using ?
- puchrova
  New Member
  5 years ago
  I need the cummulative amount to be calculated based on the price from highest to lowest. It means, the furst cummulative total will be just the total for the highest price.

Forum Discussion

Cumulative chart based on sorted column

7 Replies

Related content

Cumulative Total

Sort by another column

Column Chart sorting

Sorting

Table Sort , sort order , comparer