cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 12
Highlighted
Super User VII
Super User VII

Re: Sum in quarter

@martinap  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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Frequent Visitor

Re: Sum in 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.

Super User VII
Super User VII

Re: Sum in quarter

@martinap  hmmm not sure why, if possible, please share data (trim down version) and remove any sensitive information to get the solution.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Frequent Visitor

Re: 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

 

    

Highlighted
Frequent Visitor

Re: Sum in quarter

Annotation 2019-03-06 184242.png

 

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

Highlighted
Frequent Visitor

Re: Sum in quarter

@parry2k : shall I add something else?

 

Highlighted
Super User VII
Super User VII

Re: Sum in quarter

@martinap  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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Frequent Visitor

Re: Sum in quarter

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 😞

Highlighted
Super User VII
Super User VII

Re: Sum in quarter

@martinap trying to see what we can do here, may be just get sample data of few quarters with minimum column, do you think that is possible?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors