Forum Discussion
Cumulative Sum by Group Issue
Hi,
I'm new to Power BI having come over from the Spotfire world.
I have two tables (Main) and (Manufacturing) inside the PBIX file.
https://drive.google.com/file/d/1nsB9aREHu_007VNNZW6jBHXSnSuF5jnG/view?usp=share_link
I would like to, compute a simple running total/cumulative new column using Rate and Date and grouped by ID to end up with a table like the picture shown.
I've tried a few ways, but can't get a proper result and right now just get the Total of each Group (Name) as a single value (202+338+174=604). The "Excel Run Total" column in the manufacturing table shows what I want to achieve, but I want to do it in Power BI. Clearly I'm missing something, but here is the syntax I'm using.
PBI Run Total =
VAR MaxDate = MAX('Manufacturing'[Date])
RETURN
CALCULATE(
SUM(Manufacturing[Rate]),
FILTER(
ALLSELECTED(Manufacturing),
'Manufacturing'[Date]<=MaxDate
)
)
Thanks for your help!
Hi GeeWhiz7 ,
I have created a simple sample, please refer to it to see if it helps you.
Create a column.
PBI Run Total = CALCULATE(SUM(Manufacturing[Rate]),FILTER(Manufacturing,Manufacturing[ID]=EARLIER(Manufacturing[ID])&&Manufacturing[Date]<=EARLIER(Manufacturing[Date])))Or a measure.
Measure = CALCULATE(SUM(Manufacturing[Rate]),FILTER(ALL(Manufacturing),Manufacturing[ID]=SELECTEDVALUE(Manufacturing[ID])&&Manufacturing[Date]<=SELECTEDVALUE(Manufacturing[Date])))If I have misunderstood your meaning, please provide your desired output with more details.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
GeeWhiz7 , Always use a separate date table in such cases , joined with date of date of your table
PBI Run Total =
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE(
SUM(Manufacturing[Rate]),
FILTER(
ALLSELECTED(Date),
'Date'[Date]<=MaxDate
)
)Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41
thank you for looking at this amitchandak.
I watched the video and created a separate Date Table with unique Date Values and then modified the syntax to below, but now I only get a repeat of the rate from the Manufacturing table.
I read this as,
- declare var and assign the max date from date table to var
- calculate the sum of rate from the manufacturing table filtered for all selected dates in the date table where those dates are less than the max variable.
Not sure what I'm not understanding
Hi GeeWhiz7 ,
I have created a simple sample, please refer to it to see if it helps you.
Create a column.
PBI Run Total = CALCULATE(SUM(Manufacturing[Rate]),FILTER(Manufacturing,Manufacturing[ID]=EARLIER(Manufacturing[ID])&&Manufacturing[Date]<=EARLIER(Manufacturing[Date])))Or a measure.
Measure = CALCULATE(SUM(Manufacturing[Rate]),FILTER(ALL(Manufacturing),Manufacturing[ID]=SELECTEDVALUE(Manufacturing[ID])&&Manufacturing[Date]<=SELECTEDVALUE(Manufacturing[Date])))If I have misunderstood your meaning, please provide your desired output with more details.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you to each person providing a solution as it helps me better understand the syntax. I think the easiest one for me to use was the v-polly-msft solution mainly because it is explicit in what it is filtering (manufacturing ID and date columns) but now I understand how to make each work.
