Cumulative chart based on sorted column

[puchrova]

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

[amitchandak]

@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]

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. 

[PhilipTreacy]

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]

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 · Accepted solution]

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]

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]

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?