The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
THANK YOU SO MUCH !!! The Source was the problem, it works brilliantly, again thank you for your time and aptience.
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 |
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.
Without usable data that can be copy/pasted, I cannot provide code, but you can find your solution here:
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 |
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:
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
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?
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 |
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:
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?
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).
I hav done this can you please let me know if you can access it?
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.
THANK YOU SO MUCH !!! The Source was the problem, it works brilliantly, again thank you for your time and aptience.
Still works