Cumulative Total Question

DAX DAX Calculations
1

I am struggling to get the cumulative total to work for this setup. I have gone through the videos and no matter what I do, I am not getting a cumulative total.

Here is the data

image
image.png968×384 40.1 KB

This is my measure:

Cumulative Total = CALCULATE(
[TotalLosses],
FILTER(
ALL(‘Dates’),
Dates[Date] <= max(Dates[Date])))

This is the measure TotalLosses

TotalLosses = sum(LossData[ rptdLosses])

image

I have a date table and it has a relationship to the losses table on accident date.

image
image.png1370×574 31.6 KB

What am I missing? I am sure I am missing a fundamental concept but no matter what I read and try I keep getting the same result as TotalLosses for Cumulative Total rather than a cumulative total. After I figure this out, I need to actually do the cumulative total by Claim number so that for each claim number it adds up the losses to date for that claim.

Thanks in advance for any help that you can give me.

Dawn

2

Hi Dawn,

This is a pretty simple one.

You have the right formula, just not the correct context of the calculation.

Instead of accident_date column in the table you require the dates column.

If you have a look at the formula and the FILTER part. You are removing context from the date table. But in your table there is no column from the actual date table. So this part of the formula is doing nothing in the current state.

Also instead of using all, consider using ALLSELECTED also.

See what I mean here

3

Ahh - thank you! Just that simple comment cleared up a fundamental misunderstanding. Now it is working but with lots of empty rows - where there are dates in the date table with no match in the Losses table. This will be my next challenge to understand and perhaps the video you picked will help out there as well.

image
image.png1117×760 46.3 KB

Thanks for reaching out!
Dawn

4

Ok great.

The reason why there are blank values again is for exactly the same reason. The context of the calculation is causing this due to the claim_no being from the fact table you have.

The concept of context sometimes takes a while to sink in but is absolutely crucial.

Here’s some good videos to review here, starting here

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000594

5

I will start with that video. I get the same result even when I remove the claim number. Hopefully, the video will help me sort that out. This is my current calculation based on your feedback

Cumulative Total = CALCULATE(
    [TotalLosses],
         FILTER(
             ALLSELECTED('Dates'),
             Dates[Date] <= max(Dates[Date])))

and this is the result that I get.

image
image.png901×1126 65.5 KB

I am sure once I understand all of these basic concepts, it will all seems so easy. Off to watch more videos in hopes to eventually master this!

Thanks again for your help!
Dawn

6

Ok yes have a review of context. It’s key.

The results in your table are calculating as they should based on the formula you have.

If you need something different then add it to this post here.

7

Thanks so much - the video’s are fantastic! The only thing I would like to get rid of is the repeating rows. There are only a few rows in the sample fact table but I am getting one row per each day in the date table for the year. This makes it hard to discern the real data values with all of the repeating rows. I would really like to only have one row per real data row and the cumulative total. The is the big thing that I can’t seem to figure out. Ideally, it would Iook like the below but with the cumulative total correct. I feel like once I understand the context more clearly, adding the claim_no to the mix won’t be that hard.

image
image.png746×436 17.7 KB

Thanks,
Dawn

8

Can you try to add some logic around this formula.

It need to be like this.

Cumulative Sales = 
VAR CumulativeTotal = CALCULATE( [Total Sales],
                        FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX( Dates[Date] ) ) )
RETURN
IF( ISBLANK( [Total Sales] ), BLANK(), CumulativeTotal )

You would need to sub total losses in for the total sales measure that I have.

See how you go with this

9

This was perfect and it worked brilliantly! Thank you so much - I’ve only been a member a few days and have already learned so much!

Thanks!
Dawn

10

That’s great. Good to hear