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

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.