Forum Discussion

GeeWhiz7's avatar
GeeWhiz7
Regular Visitor
3 years ago
Solved

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_007VNNZW6jBHXSn...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

  • GeeWhiz7's avatar
    GeeWhiz7
    Regular Visitor
    3 years ago

    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