CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Pratik
Pratik

Reputation: 47

Cumulative Count by Category in Power BI

I want to get Cumulative count of rows in a Table for each month.

Suppose I have a Table like this:
Source Table

I am looking for this output:
Result

For eg : As per the Source Table, the count for Proj1 in Jan was 2 and Feb was 1. But I want the Count to be shown in Feb as 3, I want the Cumulative count.

I tried using DATESYTD, but its not giving the cumulative count.

The below example too giving me incorrect values :
Eg

Can someone advice me where I am going wrong ? Help will be much appreciated.

Thank You,
Pratik

Upvotes: 1

Views: 2056

Answers (1)

Marco_CH
Marco_CH

Reputation: 3294

You could try:

let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzzJU0lFyBGIDQz0gMjIwMgJyvBLzFICMWB2EGieoGiOYGrfUJGQ1ICFnqBpjmBrfxCJdNHNcCNhlDBRyJcIuNwJ2gcxxJ8IuDwJ2mQCFPF0IWAZS5IXLslgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, ID = _t, Date = _t, #"Date(Short)" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"ID", type text}, {"Date", type date}, {"Date(Short)", type date}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Category", "Date(Short)"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
        #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Category", Order.Ascending}, {"Date(Short)", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Row", 1, 1),
        RunTotal = Table.AddColumn(#"Added Index", "Running Total", each let Group=[Category], Row=[Row] in
                            List.Sum(Table.SelectRows(#"Added Index", each [Row]<=Row and [Category]=Group)[Count])),
        #"Removed Columns" = Table.RemoveColumns(RunTotal,{"Count", "Row"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "OtherCol", each "Just random string"),
        #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom", {{"Date(Short)", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Added Custom", {{"Date(Short)", type text}}, "de-CH")[#"Date(Short)"]), "Date(Short)", "Running Total", List.Sum),
        #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"01.01.2022", "01.02.2022", "01.03.2022"})
    in
        #"Replaced Value"

Output:

enter image description here

Upvotes: 1

Related Questions