Forum Discussion
Sum in quarter
Super UserAnonymous do you have calendar/date dimension in your model, it is best practice to have one for time intelligence calculation like yours. you can always use TOTALQTD DAX function to get total by quarter.
Hi!
I have both in my Experiment Data and Installed Base data a column with days, which is related to the list.
By using the TOTALQTD I was not able to obtain the right value.
- parry2k
Anonymous hmmm not sure why, if possible, please share data (trim down version) and remove any sensitive information to get the solution.
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