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

Hi @parry2k,

 

Here's an Excel as an example. Each tab is obtained from different queries. I have added a relationship between the list-days and all the other days in the example.

 

Excel example

Highlighted

>.< Can someone please help me? 😞

Highlighted

@martinap i just looked at your sample file but few things are not clear to me. I'm available to help, just send me private message to set up a time to connect so that we can work on it together, i'm available in pacific time zone. Thanks






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!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors