cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 looks like the following:

Cumulative Hours spend =
CALCULATE (
sum('OVERUREN_WEEK'[Hours Spend]);
FILTER (
ALL ('OVERUREN_WEEK'[Year_Week]);
'OVERUREN_WEEK'[Year_Week] <= MAX ( 'OVERUREN_WEEK'[Year_Week )
)
)

 

The problem i'm having is that the cumulative measure resets when an Employee transfers to a different function or cost center.

So for example:

 

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                   - 2

 

The cumulative restarts when the employee switches to a different Cost_center in week 201703.

The only way I was able to resolve was by removing Cost_Center and Funciton from the grid, But I dont want to do that.

 

Any help would be greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Cumulative total ignoring certain table columns

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] )
    )
)

1.PNG

 

Best 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

8 REPLIES 8
Highlighted
Microsoft
Microsoft

Re: Cumulative total ignoring certain table columns

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] )
    )
)

1.PNG

 

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] )
    )
)

2.PNG

 

Best 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.
Highlighted
Frequent Visitor

Re: Cumulative total ignoring certain table columns

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.

Highlighted
Microsoft
Microsoft

Re: Cumulative total ignoring certain table columns

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.

1.PNG

 

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

 

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.
Highlighted
Frequent Visitor

Re: Cumulative total ignoring certain table columns

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.

Highlighted
Microsoft
Microsoft

Re: Cumulative total ignoring certain table columns

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

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.
Highlighted
Frequent Visitor

Re: Cumulative total ignoring certain table columns

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.

Highlighted
Microsoft
Microsoft

Re: Cumulative total ignoring certain table columns

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] )
    )
)

1.PNG

 

Best 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

Highlighted
Frequent Visitor

Re: Cumulative total ignoring certain table columns

Yes! This works!

 

Thanks for helping me out.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors