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,

 

Based on the sample in my above post, what is your desired result? Could you illustrate your requirement with examples? If possible, please post an image.

 

Regards,
Yuliana Gu

koenmilt's avatar
koenmilt
Frequent Visitor
8 years ago

Hi v-yulgu-msft,


I'm having trouble uploading an image. 

 

The result I want is the following:

 

Employee  - cost center - function      - year_week - hours spend - cumulative

Henk         -  2500          - Developer   - 201701      - 3                   - 3

Henk         -  2500          - Developer   - 201702      - 1                   - 4

Henk         -  4000          - Developer   - 201703      - 2                   - 6

Henk         -  4000          - Consultant  - 201704      - 3                   - 9

Jan            -  3000          - Manager     - 201701      - 1                   - 1

Jan            -  3000          - Manager     - 201702      - 2                   - 3

 

 

Thus, the cumulative only breaks by employee. As I showed in the previous post, currently the cumulative also breaks by Cost_center and function, wich means the cumulative resets when Henk switches to a different cost center in week 201703.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    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

    • koenmilt's avatar
      koenmilt
      Frequent Visitor
      8 years ago

      Yes! This works!

       

      Thanks for helping me out.