Forum Discussion
Running totals per week
Greetings,
I'm trying to achieve a running total of value of certain varieties of plant per week. I have here a master file with all said varieties, the marketprice per kg of each week, and the amount of fruit harvested per week in kg.
What I tried so far:
1) Made a column with a function that basically multiplies my harvest column with the price per week column to arrive at a form of "total sales"
2) I created a quick measure for running totals, where I put the week numbers in "field" and the "total sales" column from step 1 in base value.
3) plopped down a line chart, added week numbers to X axis, and the running totals measure from step 2, and added varieties to the legend.
The resulting graph bears no resemblance to any expectations, with certain lines flying well and truely "off the chart" to use that familiar Hollywood lingo known from the movies.
Something is amiss. What could it be?
8 Replies
- amitchandak
Super User
Anonymous , Please share the measure you have created
With a Separate Week/Date table, you can have a running total like
Calculate([Your Measure] , filter(all(Week]) , Week[Year Week] = max(Week[Year Week]) ) )
Hi amitchandak,
Thanks for your input. All I did is create a "quick measure" for running totals. It's a preset measure.
Also, I'm not a programmer of any sort, and I'm still learning my way into dax formulas and the like. I'm not sure I know what you mean with a seperate week/date table. For clarification, I get my production files from growers who fill these in per week (every week has a column). It's not strictly a date or anything. I untranspose the week columns, set the data type to whole number, and use that for my X-axis.
- lbendlin
Do you want the running total to reset at the beginning of each week?
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi Ibendlin,
Thank you for your reply. I don't want the running total to reset at the beginning of each week. I just want the cumulative version of "kg per week" times "marketprice for said crop in that week" for the whole season.
I will try a few things and if that won't work, I will try to make some sample data.
- Ashish_Mathur
Hi,
You must have a Calendar table with week numbers there. If that is in place, then share the download link of the PBI file.
Hi Anonymous,
Great to hear that it's working as expected on your end!. I would suggest accepting your approach as the solution so that it can benefit others as well. It would be really helpful for others in the community who might be facing similar issues and can address them quickly.
Thanks & Regards,Prasanna Kumar
