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

Re: Cumulative Total

Hi,

 

I tried the measure stated in the chosen solution (by Sean). This works!

 

My measure is as follows:

 

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

 

In my report I want to have a table visual that has three attibutes/columns, being: Year_week, Cost_center and Employee.

With the current DAX, the cumulative kinda works, it is cumulative by year_week, and breaks by all other attributes in the table visual (so Cost_center and Employee).

However, I only want it to break by Employee, it should keep cumulate when an employee switches cost_center.

 

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 from the table, But I dont want to do that.

Highlighted
New Member

Re: Cumulative Total

Hi,

 

I am trying to do something similar to this - a 12 month rolling sum of sales that I can then show by month (ie september'17 will show october'16-september'17, october'17 will show november'16 - october'17, etc). I have managed to get this to work by modifying the formula in post 2. However, when using this, no filters work on my data - If I try to filter by product, customer gender, sales office, the numbers do not move. I have tried numerous filter variations but simply cannot get this to work. Can anyone offer any suggestions?

 

P

Highlighted
New Member

Re: Cumulative Total

Hi,

 

I am trying to do something similar to this - a 12 month rolling sum of sales that I can then show by month (ie september'17 will show october'16-september'17, october'17 will show november'16 - october'17, etc). I have managed to get this to work by modifying the formula in post 2. However, when using this, no filters work on my data - If I try to filter by product, customer gender, sales office, the numbers do not move. I have tried numerous filter variations but simply cannot get this to work. Can anyone offer any suggestions?

 

P

Highlighted
Regular Visitor

Re: Cumulative Total

Hi,

 

I am facing problems with getting the right DAX expressions.

 

The Running Total column is calculated manually... this is the result that I want to achieve with the Running Total Measure. The Running Total sums up for each Team and across the dates.

 

Capture.PNG

 

I'm using the current DAX expression, but it is running total regardless of the Team.

 

Running Total Measure =
    CALCULATE(
        SUM(Sheet1[Qty]),FILTER(ALL(Sheet1),Sheet1[Date]<=MAX(Sheet1[Date])))

 

Can someone help? Thanks.

Highlighted
Anonymous
Not applicable

Re: Cumulative Total

Hello; I am trying to create a table just like this. I followed the DAX but could not go through. Can you help me with this?

I already have the formila but cannot get it right.

 

Untitled.jpgUntitled2.jpg

Highlighted
Frequent Visitor

Re: Cumulative Total

It looks good to me, try using ; instead of ,

Highlighted
Frequent Visitor

Re: Cumulative Total

@Sean What if we have multiple rows for same date?

Highlighted
New Member

Re: Cumulative Total

Hi,

 

I have been trying to cumulative sum for a long time, but I couldnt it. I give the example below, İf you can help me I realy will be grateful. Thank you for your considiration. God bless you.

I try to 2017-01 + 2017-02 and try to write new sheet . I give you excel example. 

1.JPG2.JPG

Highlighted
Regular Visitor

Re: Cumulative Total

The pattern for cumulative totals in measures seems pretty well established using the formulation "<= MAX("

 

From testing it out myself, it appears that the MAX function in this case evaluates in the Row Context. 

 

CheckMax formula.JPG

 CheckMax table.JPG

 

 

 

 

 

What I don't understand is why is does this rather than evaluate in the Filter Context, which the description seems to imply it will do:

 

maxfunc.JPG

 

 

Highlighted
Frequent Visitor

Re: Cumulative Total

Hello,

I've found your answer since I was struggling to build a cummulative column. However, I'm still struggling with the following:

Capture.JPG

1) RUNNING_NMV_FINAL  = MEASURE = 

IF(AND(ISFILTERED('dim pm'[payment]),ISFILTERED('DIM TIER'[TIER])),
CALCULATE (
SUM (Table2[NMV]),
FILTER(ALLEXCEPT(Table2,Table2[PM],Table2[TIER]),Table2[Index] <= MAX ( Table2[Index])
)
),
calculate(sum(Table2[NMV]),filter(all(Table2),Table2[Index] <= max(Table2[Index])))
)
If you notice, I have filters selected. And this measure only works when I have, either, one selection per filter or when no selection is done in ANY filter. 
 
2) Running nmv COLUMN  = column
CALCULATE (
SUM (Table2[NMV]),
ALL (Table2),
Table2[Index] <= EARLIER (Table2[Index])
) --> followed your logic
 
Although it is displaying the running total with no slicers selected, when I start to filter, it will not change dynamically as I need. 
 
In the end of this, what I would need to do is to divide each row of this cummulative_sales by the TOTAL_SALES_AMOUNT, which will be a %. Then I will do the same for the Losses (all the process before mentioned).
 
I need this because I want to create a line chart with 2 measures (cummulative sales for X axis and cummulative losses for Y axis) and on the legends I would put the country (so, 1  line per country). But the line chart does not allow me to put measures on the X axis, hence my need of using columns.
 
Can anyone help me please? I am starting to give up on this :(.
 
Thank you!!!
 

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