Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cumulative Totals

I was created cumalative totals with the formula:

total_business(cumulative) =
CALCULATE (
SUM ( business_latest[total_business] ),
FILTER (
ALL ( 'business_latest' ),
'business_latest'[Date since registered] <= MAX( ( 'business_latest'[Date since registered] )
)
))

But my result return like this:
Capture.JPG

Instead of sum the total up by date(s), but the return result like 708 * Total business, eg:
25/10/2017 total Business 3. cummulative total should be 3, but return as 3x708 = 2124
1/11/2017 total Business 114. cummulative total should be 3+114 = 117, but return as 114x708 = 80712

Anyone know why?

Thanks

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

This is a sample table with calculated column [weekend_WED]:

weekend_WED = Tabelle1[Date1]-MOD(Tabelle1[Date1]-5;7)+6

 

 

1.PNG

 

and the measure for cumulative:

total(kum) = CALCULATE
                     (SUM(Tabelle1[total]);
                      FILTER(ALL(Tabelle1);
                      Tabelle1[Date1]<=MAX(Tabelle1[Date1])))

 

 

2.PNG

View solution in original post

Hi @Anonymous,

 

In your scenario, maybe you need to replace [Date since registered] with [week_end_day (wed) ]  in running total measure.

total_business(cumulative) =
CALCULATE (
SUM ( business_latest[total_business] ),
FILTER (
ALL ( 'business_latest' ),
'business_latest'[week_end_day(wed)] <= MAX( ( 'business_latest'[week_end_day(wed)] )
)
))

Regards,

Yuliana Gu

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

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

total_business(cumulative) =
CALCULATE (
SUM ( business_latest[total_business] ),
FILTER (
ALLSELECTED ( 'business_latest' ),
'business_latest'[week_end_day (wed)] <= MAX( ( 'business_latest'[week_end_day (wed)] )
)
))
?
Anonymous
Not applicable

The formula also not working from my end. May I know what's wrong

 

Capture.JPG

Anonymous
Not applicable

pls show us the measure that was used bei this table.
Anonymous
Not applicable

This is the formula I used:

total_business(cumulative) =
CALCULATE (
SUM ( business_latest[total_business] ),
FILTER (
ALL ( 'business_latest' ),
'business_latest'[Date since registered] <= MAX( ( 'business_latest'[Date since registered] )
)
))

Then second formula to sum up my weekly total (ended on Wed)
[week_end_day (wed) ] = 'business_latest'[Date since registered]-MOD(business_latest[Date since registered]-5,7)+6

Capture.JPG

And this is what I get. 


Appreciate your help.

Hi @Anonymous,

 

In your scenario, maybe you need to replace [Date since registered] with [week_end_day (wed) ]  in running total measure.

total_business(cumulative) =
CALCULATE (
SUM ( business_latest[total_business] ),
FILTER (
ALL ( 'business_latest' ),
'business_latest'[week_end_day(wed)] <= MAX( ( 'business_latest'[week_end_day(wed)] )
)
))

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Solved, thanks everyone.

Anonymous
Not applicable

It doesn't matter if [week_end_day (wed)] is a calculated column, i think.
Anonymous
Not applicable

This is a sample table with calculated column [weekend_WED]:

weekend_WED = Tabelle1[Date1]-MOD(Tabelle1[Date1]-5;7)+6

 

 

1.PNG

 

and the measure for cumulative:

total(kum) = CALCULATE
                     (SUM(Tabelle1[total]);
                      FILTER(ALL(Tabelle1);
                      Tabelle1[Date1]<=MAX(Tabelle1[Date1])))

 

 

2.PNG

Anonymous
Not applicable

I tired follow what you shown here, but still not working for me, maybe the date format caused the problem?

Anonymous
Not applicable

I recommend making this a measure instead of a calculated column.  The below solution provides you with a simplistic solution that may require tweaking to take into account more complex scenarios.

 

The below doesn't take into account any slicers you may have set up and totals your entire "Total Business" from beginning until the date of context.  If you put this measure into a matrix or table visual, with dates as your rows.  That will form the basis of the context.

 

total_business(cumulative) = var dateUntil = LASTDATE('business_latest'[week_end_day (wed)])
RETURN
CALCULATE (
	SUM('business_latest'[total_business]'),		
	ALL('business_latest'),
	'business_latest'[week_end_day (wed)] <= dateUntil
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors