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

Cumulative sum of a measure

Hi

 

I have my facts table (fSales) and dates table (dDates) and a measure calculating the cumulative number of sales. My meassures looks like this 

 

Licenses_sum = SUM(fSales[Licenses_sold])
 
Licenses_Running Total =
CALCULATE(
[Licenses_sum];
FILTER
(ALLSELECTED(fSales);
fSales[OrderCreationDate]<=MAX(fSales[OrderCreationDate])
)
)

 

When I create a table with the running total across years I'm getting the correct results. (table on the left below)

 

However, when I create a matrix and I ask for the running total by service plan (row) and year (column) then I'm getting incomprehensible results.

 

why does this happen? it is as if the implicit filter which is the service plan does not apply to the measure. 

 

I apprecciate your help.

 

Picture1.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Cumulative sum of a measure

Hi @chronis69,

 

Please check the following steps as below.

 

1. Create a date table and a calculated column in the date table.

 

date = CALENDARAUTO()

 

YEAR = YEAR('date'[Date])

2. Create a calcualted table.

 

y = VALUES('date'[YEAR])

3. Create the relationships between tables.

2.PNG

 

4. Create the measure to get the cumulative running total.

 

 

Licenses_Running Total matrix = 
VAR maxyear =
MAX ( 'y'[YEAR] )
VAR total =
CALCULATE (
SUM ( fSales[Licenses_sold] ),
FILTER ( ALL ( 'date' ), 'date'[YEAR] <= maxyear )
)
RETURN
IF ( ISBLANK ( total ), 0, total )

Then we can get the result as below.

3.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chronis69 Regular Visitor
Regular Visitor

Re: Cumulative sum of a measure

@v-frfei-msft@AlB

 

thanks for your help.

 

Frank thanks for the solution. Fiddling about I found another way that solved the problem. I replaced ALL with ALLEXCEPT.

 

this prevented the measure calculation from removing the implicit filter applied by [service plan 2] and hey !!! it worked.

 

Licenses_Running Total =
CALCULATE(
[Licenses_sum];
FILTER
(ALLEXCEPT(fSales;fSales[Service Plan 2]);
fSales[OrderCreationDate]<=MAX(fSales[OrderCreationDate])
)
)
7 REPLIES 7
Super User
Super User

Re: Cumulative sum of a measure

Hi @chronis69

Can you show the structure of your tables? Or share the pbix if possible

chronis69 Regular Visitor
Regular Visitor

Re: Cumulative sum of a measure

@AlB here is the structure of the tables.

 

 Picture2.png

Super User
Super User

Re: Cumulative sum of a measure

@chronis69

 

I cannot see the capture well. Could you share the pbix, if you don't have confidential data?

Super User
Super User

Re: Cumulative sum of a measure

@chronis69

Are you using slicers as well or just the table/matrix visuals shown?

Are there relationships between your tables?

chronis69 Regular Visitor
Regular Visitor

Re: Cumulative sum of a measure

@AlB unfortunatelly, it does contain confidential data.

 

I have randomised the sales numbers but the pbix contains all sort of sensitive information that is quite difficult to remove. 

 

No, I'm not using any slicers. just the two tables shown.

 

yes there is a relationship between dDates[Date] and fSales[OrderCreationDate].

 

see the ppt at the link below, I have a screenshot of the table structure that is super clear and the screenshot of the relationship present.

 

https://drive.google.com/file/d/1Wr9l6dg5rkNlzochfAT7vZLptw0HGZxY/view 

Community Support Team
Community Support Team

Re: Cumulative sum of a measure

Hi @chronis69,

 

Please check the following steps as below.

 

1. Create a date table and a calculated column in the date table.

 

date = CALENDARAUTO()

 

YEAR = YEAR('date'[Date])

2. Create a calcualted table.

 

y = VALUES('date'[YEAR])

3. Create the relationships between tables.

2.PNG

 

4. Create the measure to get the cumulative running total.

 

 

Licenses_Running Total matrix = 
VAR maxyear =
MAX ( 'y'[YEAR] )
VAR total =
CALCULATE (
SUM ( fSales[Licenses_sold] ),
FILTER ( ALL ( 'date' ), 'date'[YEAR] <= maxyear )
)
RETURN
IF ( ISBLANK ( total ), 0, total )

Then we can get the result as below.

3.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chronis69 Regular Visitor
Regular Visitor

Re: Cumulative sum of a measure

@v-frfei-msft@AlB

 

thanks for your help.

 

Frank thanks for the solution. Fiddling about I found another way that solved the problem. I replaced ALL with ALLEXCEPT.

 

this prevented the measure calculation from removing the implicit filter applied by [service plan 2] and hey !!! it worked.

 

Licenses_Running Total =
CALCULATE(
[Licenses_sum];
FILTER
(ALLEXCEPT(fSales;fSales[Service Plan 2]);
fSales[OrderCreationDate]<=MAX(fSales[OrderCreationDate])
)
)