Assume quantity sold date by date and City in a three column database. The objective is to determine year wise, month wise and City wise running total of quantity sold in a Pivot Table.
The issue which will arise with generating this result in a Pivot Table will be that the Show Values As > Running Total in, resets the quantity sold to 0 when the year changes.
This issue can be overcome by writing DAX formulas in a Power Pivot. You may refer to my solution in this workbook.
Given the table below, What is the best function to accomplish the following: If apple or orange, multiply by 2, if water or coffee, multiply by 3, if Detergent, multiply by 5.
Product Sales
Apple 2
Orange 3
Water 1
Coffee 5
Detergent 3
Can the Calculatetable or Switch functions be used to perform this task?
Do you have examples of Calculatetable or Switch?
Hi,
I guess you want to do this in the PowerPivot tool. You may create another two column table with unique products and their respective multiplying variables. Then establish a relationship between the two tables and then write the RELATED function to bring over the multiplying variables into the main table. Last not but least, multiply the two columns of numbers.
Hope this helps.
Hi, i have this exact issue but i don’t see what you did exactly with your DAX formula.
Would it be possible to use this method to show runing total and difference from previous date across years ?
Hi,
Please share some data and show the expected result. Share the link from where I can download your workbook.
Hi!
Really nice solution, thank you!! I found a small issue if you have multiple project running different start and end dates and import up-to-date data regularly, you want to extent the calendar to a (much) bigger range (I extended up to 2030).
Now, the pivot table shows all the coming months and years. I would like to clip the chart is no data is available.
Can this be done by changing/adding to the power pivot formula?
Best,
Cor
Hi,
Please share the link from where i can download your workbook. Clearly show the problem there and the expected result.
Dear Ashish,
Thanks for getting back at me.
To keep it simple, I downloaded your workbook above and adapted it to show the issue. Hope you can access the adapted workbook.
Adaptation:
Extended the date range in the calendar (table 2) a few moths and refreshed the pivot table.
Obtained result:
Pivot table/chart date range is extended according to the calendar extension (table 2). The accumulated value CumCuCnt stays constant over the extended date range (which is accurate but not relevant for me and distorts the charts’ readability).
Desired result:
Pivot table/chart stops at the last date of table 1 (ignoring the range of the calendar dates).
You are welcome. Seems like you shared the wrong file with me. Nevertheless, since i understood your question, i downloaded the file from my Blog and extended the date range in the Calendar table till March 13, 2015. I thereafter, edited my formula to restrict the Pivot Table view only till the date till where there is data. You may download the revised solution workbook from the link in the Blog article itself.
Hope this helps.
That did the trick! Thanks!!
You are welcome.
Compute “running total in” across years in a Pivot Table
{ 10 Comments }