sum the totals per day in a column

[Julier]

Hi have 3 columns of data, one column has years 2022, 23 and 24, for December,  the second column has the day the sale was made in the month and the 3rd column has the sqm sold, I have many sales for each day  which i want to be able to show as a total for the day for each year and then this would allow me to chart as below.

 

[ronrsnfld · Accepted solution]

I have accessed it and sent you a link to the pbix file for you to look at.

The code I provided (above) seems to be returning the correct values and totals as you can see below:

Totals for each year/month:

Graphic output

I'm not sure why you are having an issue applying the code. But please let me know if you figure the issue.

[Julier]

Hello, thank you, I have added the table below.

Calendar Year Month	Day Number in Calendar Month	Volume (SQM)
2023-12	12	44,112
2024-12	11	18,421
2022-12	16	10,131
2023-12	14	28,784
2022-12	7	0
2022-12	9	20,765
2023-12	5	16,597
2022-12	19	27,221
2023-12	20	40,610
2022-12	11	24,552
2024-12	11	5,368
2022-12	6	13,664
2024-12	2	13,326
2022-12	12	35,731
2022-12	13	42,042
2022-12	9	9,019
2024-12	10	5,459
2023-12	4	1,823
2022-12	21	5,904
2023-12	4	807
2022-12	5	5,100
2023-12	3	5,239
2023-12	1	3,200
2022-12	15	9,486
2022-12	6	24,707
2023-12	5	3,492
2023-12	26	13,099
2022-12	9	31,117
2023-12	22	3,379
2024-12	10	4,808
2024-12	5	22,057
2023-12	19	20,278
2022-12	8	8,474
2022-12	21	1,913
2023-12	15	0
2023-12	4	3,616
2022-12	7	0
2022-12	14	5,659
2022-12	12	3,874
2023-12	15	1,962
2022-12	30	28,669
2024-12	6	21,052
2024-12	11	3,747
2023-12	14	10,477
2022-12	15	24,524
2023-12	13	44,961
2022-12	19	38,004
2022-12	18	26,815
2024-12	4	19,112
2022-12	16	28,649
2023-12	14	47,369
2022-12	12	39,446
2022-12	1	10,981
2022-12	2	14,141
2022-12	12	5,035
2023-12	21	2,382
2023-12	20	17,735
2024-12	4	1,403
2022-12	20	29,069
2023-12	14	64,110
2022-12	1	10,725
2024-12	9	4,018
2022-12	29	16,609
2022-12	4	579
2023-12	5	4,724
2024-12	4	13,031
2023-12	19	21,900
2023-12	15	15,371
2022-12	13	9,717
2023-12	19	29,492
2022-12	1	6,799
2022-12	6	10,129
2022-12	1	6,385
2022-12	15	8,910
2023-12	6	38,100
2022-12	19	8,271
2022-12	5	7,650

[p45cal]

It may be possible directly from the table you have (your charts look cumulative to me):

Note that currently there are some days missing on the x-axis.

[ronrsnfld]

Without usable data that can be copy/pasted, I cannot provide code, but you can find your solution here:

Compute a Running Total by Category in Power Query 

[Julier]

Hello, thank you, I have added the table below.

Calendar Year Month	Day Number in Calendar Month	Volume (SQM)
2023-12	12	44,112
2024-12	11	18,421
2022-12	16	10,131
2023-12	14	28,784
2022-12	7	0
2022-12	9	20,765
2023-12	5	16,597
2022-12	19	27,221
2023-12	20	40,610
2022-12	11	24,552
2024-12	11	5,368
2022-12	6	13,664
2024-12	2	13,326
2022-12	12	35,731
2022-12	13	42,042
2022-12	9	9,019
2024-12	10	5,459
2023-12	4	1,823
2022-12	21	5,904
2023-12	4	807
2022-12	5	5,100
2023-12	3	5,239
2023-12	1	3,200
2022-12	15	9,486
2022-12	6	24,707
2023-12	5	3,492
2023-12	26	13,099
2022-12	9	31,117
2023-12	22	3,379
2024-12	10	4,808
2024-12	5	22,057
2023-12	19	20,278
2022-12	8	8,474
2022-12	21	1,913
2023-12	15	0
2023-12	4	3,616
2022-12	7	0
2022-12	14	5,659
2022-12	12	3,874
2023-12	15	1,962
2022-12	30	28,669
2024-12	6	21,052
2024-12	11	3,747
2023-12	14	10,477
2022-12	15	24,524
2023-12	13	44,961
2022-12	19	38,004
2022-12	18	26,815
2024-12	4	19,112
2022-12	16	28,649
2023-12	14	47,369
2022-12	12	39,446
2022-12	1	10,981
2022-12	2	14,141
2022-12	12	5,035
2023-12	21	2,382
2023-12	20	17,735
2024-12	4	1,403
2022-12	20	29,069
2023-12	14	64,110
2022-12	1	10,725
2024-12	9	4,018
2022-12	29	16,609
2022-12	4	579
2023-12	5	4,724
2024-12	4	13,031
2023-12	19	21,900
2023-12	15	15,371
2022-12	13	9,717
2023-12	19	29,492
2022-12	1	6,799
2022-12	6	10,129
2022-12	1	6,385
2022-12	15	8,910
2023-12	6	38,100
2022-12	19	8,271
2022-12	5	7,650

[ronrsnfld]

You can create a running total column in Power Query that will produce the results you show.

Create a running total function by pasting the code below into a blank query in the Advanced Editor in PQ: (Be sure to rename the query as shown)

//rename fnRT
(tbl as table, rtDataCol as text, rtOutCol as text)=>

let 
    a = List.Buffer(Table.Column(tbl,rtDataCol)),

    rt = List.Generate(
        ()=>[r=a{0}, idx=0],
        each [idx] < List.Count(a),
        each [r = [r] + a{[idx]+1}, idx=[idx]+1],
        each [r]),

    #"Add Running Total Column" = 
        Table.FromColumns(
            Table.ToColumns(tbl)
            & {rt},
            Table.ColumnNames(tbl) & {rtOutCol}
        )
in 
    #"Add Running Total Column"

Then use this code to add the running total column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVVZruQwCLxLfzMSqzFneXr3v8bYOD0TgT/aasVhqaKK/Px8GFn+EH/gk4cq0PrzC3mjzw3tY4IyfW/4uRn7QCD5d/PNpuvgCT61xPj6YXkW+2UEH1bSWBYBC6+VM8SBuVZm3DgQBtUqiYMVzK4IDWTMEpL4BMbQEsHnQnjUIvsQA5dGluzGGFD5Aj8AF6bSFmZbalEwbnIJJktJxAdHoF4CJlYSLd8mxPK25HOWWnZnF2BsxFoC0FnJGIdw/1/5NVcBDa7DewjHiAtHQkudNRVnxyB+ZU9h4iwXllGAVlPRo0P2qoO5f6BexZyEEwRJTWVvmb+msMRElaabJdJABsMqEWdCML3OOEuuXkaVl+Dx4hiVopwPLSauhhBwbRTpcbx6c6Q97uLWmJzNEqNZIqc6AbESSzPVAJOstJYNxHtNvZfRhqlNtztGffn7TuaSrjYfH5wxa88nIZA2MKktQKlLLEXCILOpfQ+GfO2KK0hQbAbPUa5dMa4Yx17fTUkHiXMtEukPpCp2jrN1B1a6UpNeS1vmca47Uh8v949DHN1F2zxHw2sT+3V7Bnjz/0kW72XyAj7A2yr5frS4qSEDZNpN2nO5vLY7jnypb8TICG4wdiZfrl4Bv38B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calendar Year Month" = _t, #"Day Number in Calendar Month" = _t, #"Volume (SQM)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Calendar Year Month", type text}, {"Day Number in Calendar Month", Int64.Type}, {"Volume (SQM)", Int64.Type}}),

//Group by Year and Day Number to get Sum of Volume for each Day Number
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Calendar Year Month", "Day Number in Calendar Month"}, {
        {"Total Volume", each List.Sum([#"Volume (SQM)"]), type nullable number}}),

//Group by year, then sort and get running total for each day
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Calendar Year Month"}, {
        
        {"all", each fnRT(
            Table.Sort(_,{"Day Number in Calendar Month", Order.Ascending})            , 
        "Total Volume","Volume Running Total")  , 
        
        type table [Year=number, Day Number in Calendar Month=nullable number, Total Volume=nullable number, Volume Running Total=nullable number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows1", "all", {"Day Number in Calendar Month", "Volume Running Total"})
in
    #"Expanded all"

Finally, Close and Apply and select the Line Chart visual from Power BI Desktop

Results:

[Julier]

Thanks for your help with this, i can get this all to work through to the chart however, some of the data is missing eg in 2023-12 there are missing day numbers from the 7th to the 11th, 23rd, 27th to 31st, plus the total of 483619 is incorrect, the total for the whole of Dec 2023 should be 23968016, on the source data if i omit the missing days that are now in the query the total is 15190109 

[ronrsnfld]

I'm not sure what you mean.

The dates that are missing were not present in your sample data, and the volume total for 2023-12 of 483,619 is correct for the data you present.

I would assume that either you did not properly adapt my algorithm to your total data, or I did not understand what you wanted to do. But with the data you presented, the totals are correct and the missing dates were missing in the sample you provided. And the graphic output is similar in shape to what you showed in your original question.

I don't have any other thoughts as to the discrepancy you are reporting. Perhaps you can explain it more clearly to me?

[Julier]

Apologies for not being clear, i was unable to send all of the data as the file was to large, when i ivot the data you will ee that the total at the end of 2022 should read 26,323,830 however the total i have using the running total is 61,0466 

Calendar Year Month	2022-12
Row Labels	Sum of Volume (SQM)
1	1,306,389
2	1,103,425
3	381,176
4	314,825
5	1,758,179
6	1,336,631
7	1,489,147
8	1,235,047
9	974,567
10	290,076
11	246,512
12	1,745,245
13	1,296,669
14	1,352,746
15	1,101,934
16	882,852
17	353,579
18	225,891
19	1,994,050
20	1,487,242
21	1,340,749
22	1,158,522
23	821,427
24	131,428
27	124,331
28	606,476
29	604,444
30	592,876
31	67,395
Grand Total	26,323,830

[ronrsnfld]

Again, what you are doing is unclear.

Below is the result of applying the Running Total function to the data you have supplied above which you have written is for 2022-12.

As you can see, the running total function results in the 26,323,380 value

As a Table:

Row LabelsSum of Volume (SQM)Running Total

1	1,306,389	1,306,389
2	1,103,425	2,409,814
3	381,176	2,790,990
4	314,825	3,105,815
5	1,758,179	4,863,994
6	1,336,631	6,200,625
7	1,489,147	7,689,772
8	1,235,047	8,924,819
9	974,567	9,899,386
10	290,076	10,189,462
11	246,512	10,435,974
12	1,745,245	12,181,219
13	1,296,669	13,477,888
14	1,352,746	14,830,634
15	1,101,934	15,932,568
16	882,852	16,815,420
17	353,579	17,168,999
18	225,891	17,394,890
19	1,994,050	19,388,940
20	1,487,242	20,876,182
21	1,340,749	22,216,931
22	1,158,522	23,375,453
23	821,427	24,196,880
24	131,428	24,328,308
27	124,331	24,452,639
28	606,476	25,059,115
29	604,444	25,663,559
30	592,876	26,256,435
31	67,395	26,323,830

Screenshot:

[Julier]

is there any way i am able to send you the file as a whole, as it i too large to put on here as a table?

[ronrsnfld]

You can upload to some sharing site, such as OneDrive or DropBox or Google Drive, and then send me the link (by PM if you want it kept private).

[Julier]

I hav done this can you please let me know if you can access it?

[ronrsnfld · Accepted solution]

I have accessed it and sent you a link to the pbix file for you to look at.

The code I provided (above) seems to be returning the correct values and totals as you can see below:

Totals for each year/month:

Graphic output

I'm not sure why you are having an issue applying the code. But please let me know if you figure the issue.

[Julier · Accepted solution]

THANK YOU SO MUCH !!! The Source was the problem, it works brilliantly, again thank you for your time and aptience.

[p45cal]

Still works