Measure, which sums the last item from each group

[Julian_12]

Hi All,

I need to a create a risk burndown chart for a product with multiple components. Components have their test or analysis completed at some point in times, which reduce the risk. My burndown chart has to show how much total risk is left at the particular time and what risk band it is (see table below). I believe that I need a measure, which will sum the [Risk total per component] of the last row for each component per date. Please note that the same component can have multiple risk reductions at the same date. I would use [Risk Band] as a legend to get the stuck-up.

- csv format

Component,Development Risk Change,Risk total per component,Test/Analysis Date,Risk Band
A,20,20,26/01/2021,amber
A,-5,15,02/05/2021,amber
A,-2,10,10/11/2021,green
A,-3,10,10/11/2021,green
B,30,30,26/01/2021,red
B,-12,18,09/10/2021,amber
B,-10,6,23/11/2021,green
C,40,40,26/01/2021,red
C,-12,28,12/05/2021,red
C,-8,20,18/07/2021,red
C,-7,13,22/11/2021,amber

The chart would like something like the follow.

[parry2k]

@Julian_12 since @negi007 is working on it, and you are in safe hands. In case it doesn't work out, just ping me. Good luck!

[negi007]

@Julian_12  in this case, i suggest you to follow below steps

in power query window, please add a index column like below

then in your power bi create a measure to find out last row value for each component

Last_row_cat = if ( CALCULATE ( MAX ( 'tab'[Index] ), ALLEXCEPT (tab, tab[Component] ) )=tab[Index], "last row", "others")

i am also attaching pbix working file for reference. 

[Julian_12]

Hi @negi007 , would it be possible to use date from the date from the [Test/Analysis Date] column as the index, as my rows are not in date order? Your formula identifies last row per component. How to make a measure from it so I could plot it against time?

[negi007]

@Julian_12 you can do the soring on multiple columns like below and then add the index column

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc47CoAwEATQu6QWNrN+sFWPIRaKwUqL3L9wN/EDasBmyfKSmfS9aUxm2MoAcUVsGXIe18l5M2SRUcooiRMaHoNgT1+8c9svb2XLn+XezRei1huEd7VqlfpXJ1sRc/GMVWON5YTZkFp8GXJtve1oHHY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Component = _t, #"Risk total per component" = _t, #"Analysis date" = _t, #"risk band" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Component", type text}, {"Risk total per component", Int64.Type}, {"Analysis date", type date}, {"risk band", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Component", Order.Ascending},{"Analysis date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)
in
#"Added Index"

here is updated file for you as well.

[Julian_12]

Hi @negi007, thank you for your answers. But how I could plot the risk burndown graph, as per orginal post  from this functions?

[parry2k]

@Julian_12 provides the data in the table format so that it can be copied, also what happens if there is a tie, two records of a component, and the same risk band.

[Julian_12]

Hi @parry2k , thank you for the question. I updated the post with the data. Each component risk will be going down with every test/analysis. If the risk go below a threshold then the whole remaing risk move to a lower band. I think it is already covered by [running total risk by component]. I already creted risk running total per component and it reduces sum of the risk on that date if it happens more than one on the date (see rows 3 and 4 for component A).