Running total with group

[Julian_12]

Hi All, I have quite complicated issue. I have the presented below data table and I need to present the total number of items per value band in stacked area chart. Please note that at each date the number of product items can change and the value band of the product running total can change as well.

The result should look like this, but I do not know how to get there in Power Bi.

[v-henryk-mstf · Accepted solution]

Hi @Julian_12 ,

According to your description, you need to use the pivot column function in the power query, and then replace the null values in it. Refer to the following screenshot.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY+xDoAgDET/hZkmbRXRUf0MwqCROOnA/w+CEURJmiaXl7trjRGjkIIwLEYmQALugti9c6ew8uEqcwXIFWdOnMLEsOVYnb/5FNE/3rstwzZ7EXCovFhEc/OpnoNS1emvPXLi4vRbpO5IdYYaqK/Nqiwv/rYX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, PurchaseItem = _t, PurchaseDate = _t, #"Value Band for product" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"PurchaseItem", Int64.Type}, {"PurchaseDate", type date}, {"Value Band for product", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Product"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Value Band for product"]), "Value Band for product", "PurchaseItem"),
    #"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"PurchaseDate", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows1",null,0,Replacer.ReplaceValue,{"green", "amber", "red"})
in
    #"Replaced Value"

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Julian_12]

Hi @v-henryk-mstf and @amitchandak 

I really tried to make your solution works but I failed. Especially that ‘Value Band per Product’ is calculated in DAX. However, I realised that this problem can be solved in a much simpler way. If I calculate running total per group (see below table), then I can calculate the band values via a measure, which would identify the last row in each group by a date and sum the running total of the identified rows. The ‘Value Band per Product’ would be a legend to separate them on the graph. I believe that I identified the way to calculate it, but I have no idea how to do it in DAX. Would you be able to help please?

Product	No of Purchesed items	Running total per product	Purchese Date	Value band for product
A	10	10	26/01/2021	green
A	15	25	02/05/2021	green
A	22	47	10/11/2021	amber
B	2	2	26/01/2021	red
B	4	6	09/10/2021	amber
B	0	6	23/11/2021	green
C	50	50	26/01/2021	amber
C	12	62	12/05/2021	red
C	7	69	18/07/2021	amber
C	15	84	22/11/2021	green

[amitchandak]

@Julian_12 , The second screenshot seems like a Matrix visual- Date on row, Value brand per item on the column, and no of the purchased item on values.

In case of stacked Area Value brand per item is a legend

[Julian_12]

Hi @amitchandak, yes, but the real question is how to calculate the second table from the first one. 

[amitchandak]

@Julian_12 , If only want to display matrix would do that ?

Date on row, Value brand per item on the column, and no of the purchased item on values.

Else unpivot the table in power query

https://radacad.com/pivot-and-unpivot-with-power-bi