Forum Discussion
Sum in quarter
Hi everybody!
It's my first time writing here :-)
I would really appreciate your help to solve this issue.
I have a table which contains these values:
DAYS | Count of number of runs | Cumulative sum of systems installed in the reference day | Number of runs/Installed systems
__________________________________________________QUARTER1___________________________________________________________________________
DAY1 | 1 | 1 | 1
DAY2 | 3 | 1 | 3
DAY3 | 1 | 1 | 1
DAY4 | 0 | 1 | 0
__________________________________________________QUARTER2___________________________________________________________________________
DAY5 | 1 | 2 | 0.5
DAY6 | 5 | 2 | 2.5
DAY7 | 0 | 2 | 0
DAY8 | 0 | 3 | 0
DAY9 | 2 | 3 | 0.67
DAY10 | 4 | 3 | 1.33
These values are calculated as follows:
DAYS: comes from a list starting from july-2016 to today
Number of runs: I have a table in which I have all the runs and it comes from COUNT([Experiment Name])
Cumulative sum of installed systems: it counts the number of systems installed and cumulate theme day by day
Number of runs/installed systems: DIVIDE([Number of runs];[Installed systems])
Let's imagine that DAY1 to DAY4 are in QUARTER1 of the year, whil DAY5 to DAY10 are in QUARTER2 of the year.
What I would like to obtain is the sum of the ratio: as an example for Q1: 1+3+1+0 and for Q2: 0.5+2.5+0.67+1.33
Since all columns comes from calculations, I have tried to perform a cumulative sum as follows:
=SUMX(FILTER(ALLSELECTED('list-days'[Days]);'list-days'[Days]<=MAX('list-days'[Days]));CALCULATE([Run/Installed systems]))
I had to put the CALCULATE, since otherwise it will make the sum of runs and divided it by the number of systems (which is actually different from summing all the ratios).
The formula works, but it continues to cumulate the sum: I would like that the cumulative sum is only for the quarters and not for the entire period.
Can someone please help me? I'm getting crazy :-(
12 Replies
- parry2k
Super User
Anonymous 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.
- AnonymousNot applicable
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
Super User
Anonymous hmmm not sure why, if possible, please share data (trim down version) and remove any sensitive information to get the solution.