Forum Discussion
HELP !! simple calculation ?!
Goodday,
I need a calculation which sums up the total of a certain column.
For example the column 'Aantal HP gerealiseerd' has a number of HP's per week but i need a column that shows the cumulative per week.
i think it's pretty simple but im not sure how to do it
For example
2018-35 186
2018-36 418 (186 + 232)
2018-37 727 (418 + 309)
2018-38 etc
Modeling -> New measure, and copy the code:
Cumulatieve HP gerealiseerd =
CALCULATE ([Totaal aantal HP];
FILTER(
ALL(Forecast);
'Forecast'[Week]<=MAX(Forecast[Week])
)It will work as a line or bar graph. Put a Date dimension on the Axis and your newly created measure to Values.
For me it looks like this:
11 Replies
It sounds like you need a semi-additive measure.
You would need a date dimension table, but then a formula like:
Total : =
CALCULATE (
[FIELD],
LASTDATE ( 'Date' [Date] )
)
I have a 'date' table so that is ok but im not sure what to fill in at the [Field] part of the calculation.
Can you tell me ? i tried a few things but it's doesn't work just yet
Cumulatieve HP gerealiseerd=
CALCULATE (
[FIELD];
LASTDATE ( 'Date'[Date] )
)
Hi ,
There several method to calculate cumulative total , however considering your sample data , this DAX will is simple way to achieve the desired output.
Your Data (table = Rve Data)
Col1 Col2 Number
2018 35 186
2018 36 232
2018 37 309
2018 38 150
2018 39 215
First of all i created a measure called 'Total Number' = sum('Rvd Data'[number])
Second Measure called Cumulative number ( Dax below)
Cumulative number = CALCULATE ([Total number],
FILTER(
ALL('Rvd Date'),
'Rvd Date'[Col2]<=MAX('Rvd Date'[Col2])
)
)Hope this helps,
Cheers
SS
BobBI thanks for the reply but it doesn't work just yet.
Ive added the measure and the formula but it just returns the same value on all rows.
The only thing i can see is that you have 2 colums for the Year-Week (Col1 and Col2) and i have one column (Week)
Totaal aantal HP = SUM(Forecast[Aantal HP gerealiseerd])
Cumulatieve HP gerealiseerd =
CALCULATE ([Totaal aantal HP];
FILTER(
ALL(Forecast);
'Forecast'[Week]<=MAX(Forecast[Week])
)
)Hi RvdHeijden,
try to create a measure out of the Cumulative HP gerealiseerd calculation instead of a calculated column.
Regards,
Barna
