Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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:

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

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    8 years ago

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

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

     

     

     

    and the measure for cumulative:

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

     

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago
        pls show us the measure that was used bei this table.