Forum Discussion
Sum in quarter
These are all the formulas that I created:
Days table:
List of days from Jul-2016 to today -> DATE format
Quarter (2016-Q3, 2017-Q4, etc) -> TEXT format
installed-base table:
Number of installed systems:
=[Installed Systems]-[Uninstalled Systems]
For cumulative sum of installed base through days
=SUMX(FILTER(ALLSELECTED('list-days'[Days]);'list-days'[Days]<=MAX ('list-days'[Days]));[Installed Base])
Experiment data:
Number of run per day
=COUNT('experiment-data'[Experiment Name])
Run/Installed Base per day:
=CALCULATE(DIVIDE([Number of run per day];[Installed Base [Day]]]))
If I plot this using DAY as X axes and Y it is ok
If I change the X axes with year-quarter, what I obtain is TOTAL NUMBER OF RUN/TOTAL NUMBER OF SYSTEMS, but I would like to have the SUM of each NUMBER OF RUN/NUMBER OF SYSTEMS per day
If I try to add the TOTALQTD to this formula, and plot it QUARTER vs. FORMULA, I obtain the same
Cumulative sum over a quarter:
=SUMX(FILTER(ALLSELECTED('list-days'[Day]);'list-days'[Day]<=MAX('list-days'[Day]));CALCULATE([Run/Installed Base [Day]]]))
The first row for the first quarter is ok, but the second quarter includes the total of the first quarter + new values and so on
For the first quarter: 29.67 is 89/3 and it is wrong
33.33 is the sum of run/installed base for each day
66.016 is the sum of run/installed base for each day, including the total from the previous quarter
- Anonymous7 years agoNot applicable
parry2k : shall I add something else?
- parry2k7 years ago
Super User
Anonymous would you mind sharing sample data in excel and get back to you, you are using running total and thats' why q2 includes q1 data and so forth so on.
- Anonymous7 years agoNot applicable
Maybe I can do the trick by subtracting the value from the previous quarter in the formula?
Unluckily it is quite difficult for me to share the Excel, since it contains a lot of data, that I cannot share :-(