Forum Discussion

Tony5's avatar
Tony5
Icon for Helper I rankHelper I
8 years ago
Solved

Running Totals with Filtering

I'm attempting to migrate a dashboard from Tableau to PowerBI, and I'm unsure how to implement a specific feature.   Basically, if I have a column for sales for the months of Jan Feb Mar, I need a ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi Tony5,

    Please create another table named "Month" including month, there is no ralationship between table Month and your sales table. 

    1. Create a sclier including Month[month] column.

    2. Create a measure to calculate the running total using the formula below.

    running total = VAR mm=SELECTEDVALUE(Month[Month])
Return CALCULATE(SUM(Test[sales]),FILTER(Test,Test[Month]<=mm))


    3. Please see the screenshot, when you click 1, it disply the sum sale of jan, when you click 2, it disply the sum sale of jan+feb. You will get expected result.

    picture1  
    Please download the attachment for more details.

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Tony5,

Please create another table named "Month" including month, there is no ralationship between table Month and your sales table. 

1. Create a sclier including Month[month] column.

2. Create a measure to calculate the running total using the formula below.

running total = VAR mm=SELECTEDVALUE(Month[Month])
Return CALCULATE(SUM(Test[sales]),FILTER(Test,Test[Month]<=mm))


3. Please see the screenshot, when you click 1, it disply the sum sale of jan, when you click 2, it disply the sum sale of jan+feb. You will get expected result.

picture1  
Please download the attachment for more details.

Best Regards,
Angelia