Forum Discussion

koenmilt's avatar
koenmilt
Frequent Visitor
8 years ago
Solved

Cumulative total ignoring certain table columns

I have the following situation:   I have a report in which i show the following: Employee Cost_center Function Year_Week Hours Spend Cumulative Hours spend   The cumulative Hours spend look...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi koenmilt,

     

    You could try  this:

    Cumulative Hours spend =
CALCULATE (
    SUM ( 'OVERUREN_WEEK'[Hours Spend] ),
    FILTER (
        ALLEXCEPT ( 'OVERUREN_WEEK', OVERUREN_WEEK[Employee] ),
        OVERUREN_WEEK[Year_Week] <= MAX ( OVERUREN_WEEK[Year_Week] )
    )
)

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi koenmilt,

 

Please create several calculated column in source table using below formulas:

Previous Const_center =
LOOKUPVALUE (
    OVERUREN_WEEK[Cost_center],
    OVERUREN_WEEK[Employee], OVERUREN_WEEK[Employee],
    OVERUREN_WEEK[Year_Week], OVERUREN_WEEK[Year_Week] - 1
)

Previous Function =
LOOKUPVALUE (
    OVERUREN_WEEK[Function],
    OVERUREN_WEEK[Employee], OVERUREN_WEEK[Employee],
    OVERUREN_WEEK[Year_Week], OVERUREN_WEEK[Year_Week] - 1
)

Is Change =
IF (
    OVERUREN_WEEK[Cost_center] = OVERUREN_WEEK[Previous Const_center]
        && OVERUREN_WEEK[Function] = OVERUREN_WEEK[Previous Function],
    0,
    1
)

Change times =
CALCULATE (
    SUM ( OVERUREN_WEEK[Is Change] ),
    FILTER (
        ALLEXCEPT ( OVERUREN_WEEK, OVERUREN_WEEK[Employee] ),
        OVERUREN_WEEK[Year_Week] <= EARLIER ( OVERUREN_WEEK[Year_Week] )
    )
)

 

Then, create a measure to return Cumulative total.

Cumulative Hours spend =
CALCULATE (
    SUM ( OVERUREN_WEEK[Hours Spend] ),
    FILTER (
        ALLEXCEPT ( OVERUREN_WEEK, OVERUREN_WEEK[Employee] ),
        OVERUREN_WEEK[Change times] = MAX ( OVERUREN_WEEK[Change times] )
            && OVERUREN_WEEK[Year_Week] <= MAX ( OVERUREN_WEEK[Year_Week] )
    )
)

 

Best regards,
Yuliana Gu

  • koenmilt's avatar
    koenmilt
    Frequent Visitor
    8 years ago

    Hi Yuliana,

     

    First of all, thanks for the reply!

     

    I have tried creating the measures you suggested. But i'm having trouble creating the first one.

     

    It says "a single value for column "EMPLOYEE" in table "OVERUREN_WEEK" cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying aggregation such as min, sum..."

     

     

    Also, in your final example (the picture) it does not seem to work, The cumulative total for Henk in Year_week 201704 is reset because he switched cost_center there.

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

      Hi koenmilt,

       

      From the highlighted rows, we can see that the cumulative total for Henk in Year_week 201704 is reset, comparing with Year_week 201703.

       

      For the error message, do you have multiple records for one employee under the same week?

       

      Regards,
      Yuliana Gu

      • koenmilt's avatar
        koenmilt
        Frequent Visitor
        8 years ago

        Hi v-yulgu-msft,

         

        I'm sorry, I probably did not explain well enough. 

        Indeed, it is reset. I am looking for a way in which the cumulative will NOT be reset in week 201704, even though the employee has a different cost center in that week.

         

        The DAX which I described in my original post has the same behaviour as the one you suggest in your post.