Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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:
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])
)
)
Hello Make sure your Week Column you are using, comes from a Date Defined Table, in other words there must be a Column with a Date Value and this week must be a derived Column of that Date (then use Date instead of Week in the Formula)
To make sure you are using a Date Defined table, right click the dataset and select Define as Date Table) in the fields section.
hi Rvd,
can you split 2018-35 by '-' ? because '2018-35' is become a text as it contains hiphen (-) sign. if you split them or use any month or date fields with numeric or date data type , then this would work.
forcast week number should be numeric . and check the values in table visual as it would summarise the amount value by week.
calcuated column wouldn't help.
Thanks,
SS
Hi @RvdHeijden,
try to create a measure out of the Cumulative HP gerealiseerd calculation instead of a calculated column.
Regards,
Barna
How ?
Besides i need this data for a bar or a line graph so does that still work if i use a measure ?
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:
@Barnee it made a few changes and now it works.
The trick was that i was still using a calculated column in the visual instead of the measure
It still doesn't work, it still returns the same value and ive copied your formula.
The measure for Totaal aantal HP is different i think because you use 2 columsn Col1 and Col2 and i have 1 colum
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] )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |