Create a cumulative line chart that will updated cumulative value based on category filters

[et6861]

Hi,

I have the below dataset. I need to create a line chart of cumulative volume, where I want to be able to filter based on Object, Object Sub-unit or Batch and the graph will update to show the cumulative volume for the objects that are selected by the filter. (i.e. I want a line graph that can show the cumulative volume provided by Objects just in Batch 1 or the cumlative volume provided by Object Sub unit A or the cumulative volume provided by all objects). I have included date but i don't need the cumulative based on date, I would just like to sort the objects by date.

I am struggling based on other posts to work out how to do this, so any help would be much appreciated.

Thanks.

Object	Object Sub-Unit	Group	Date	Volume
A-100	A	1	19/09/2010	3.36
A-100	B	1	19/09/2010	8.48
A-100	C	1	19/09/2010	16.12
A-100	D	1	19/09/2010	15.27
A-200	A	2	29/09/2010	4.73
A-200	B	2	29/09/2010	13.49
A-200	C	2	29/09/2010	9.98
A-200	D	2	29/09/2010	7.70
A-300	A	1	06/10/2010	3.59
A-300	B	1	06/10/2010	12.44
A-300	C	1	06/10/2010	12.22
A-300	D	1	06/10/2010	7.68
Z-250	A	3	25/10/2010	6.77
Z-250	B	3	25/10/2010	17.52
Z-250	C	3	25/10/2010	11.15
Z-250	D	3	25/10/2010	4.39
Z-300	A	2	30/10/2010	1.60
Z-300	B	2	30/10/2010	14.17
Z-300	C	2	30/10/2010	4.46
Z-300	D	2	30/10/2010	3.57
A-400	A	1	05/11/2010	2.33
A-400	B	1	05/11/2010	11.07
A-400	C	1	05/11/2010	12.45
A-400	D	1	05/11/2010	4.52
A-500	A	2	12/11/2010	2.35
A-500	B	2	12/11/2010	7.29
A-500	C	2	12/11/2010	15.96
A-500	D	2	12/11/2010	12.90
Z-600	A	3	14/11/2010	7.50
Z-600	B	3	14/11/2010	13.90
Z-600	C	3	14/11/2010	11.64
Z-600	D	3	14/11/2010	6.82

[tex628]

Volume=
VAR mdate = max(table[date])
VAR myear = Year(mdate)
return
Calculate(sum(table[volume]); all(table[date]); table[Year] = myear ; table[date] <=mdate)

You need to make a year column from your date column, then try this. Should work dynamically with object/object sub-unit/group being placed in normal slicers.

[danno]

Here's a similar pattern with a Calendar Table, the need for a year variable is only required if you are doing a year to date calculation. Cumulative Volume = VAR SelectedDate = MAX(Data[Date]) RETURN CALCULATE(SUM([Volume]), ALL('Calendar'), 'Calendar'[Date] <= SelectedDate)

[et6861]

Hi both, many thanks for your response. I have tried this and it seems to just calculate the cumulative volume for all objects and then have that as a single value for each object so my cumulative vol vs. object line graph is just a straight line... any dieas what i'm doing wrong? I created a measure using the formula below

Cumulative  Volume = VAR mdate = max(Sheet1[Date].[Date])return Calculate(sum(Sheet1[Volume]),ALL(Sheet1),Sheet1[Date]<=mdate)

[v-cherch-msft · Accepted solution]

Hi @et6861 

Please check if the below measure matches your request.If it is not your case,please explain more about your expected output.Attached sample file for your reference.

Cumulative  Volume = 
CALCULATE (
    SUM ( Sheet1[Volume] ),
    FILTER ( ALLSELECTED( Sheet1 ), Sheet1[Date] <= MAX ( Sheet1[Date] ) )
)

Regards,

[et6861]

@v-cherch-msft yes this has worked. Many thanks for your help!

[tex628]

@et6861  Did you meake a measure or a calculated column?

[tex628]

@dannois correct, the year column is only necessary for a YTD. My misstake! 🙂