Create a running total that changes on filtered records

[kfschaefer]

I am still pretty new to this and I need to create a process for a running totals on multipe data fields.  I tried using Group by but that does not change when the filter changes the data.  What do I need to do to the following to have the data update when the filter is applied.

let
    Source = PowerBIData,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Year", "Total Revenue", "Total Sales Expense", "Total OPS Expense", "Contrib Margin", "Total G&A Expense", "Total#(lf) Expense", "NI $ per Group", "NI % per Group"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Year"}, {{"RevenueTotal", each List.Sum([Total Revenue]), type number}, {"SalesExpTtoal", each List.Sum([Total Sales Expense]), type number}, {"OpsExpTotal", each List.Sum([Total OPS Expense]), type number}, {"ContrbMarginTotal", each List.Sum([Contrib Margin]), type number}, {"ExpTotal", each List.Sum([#"Total#(lf) Expense"]), type number}, {"NetIncome$Total", each List.Sum([#"NI $ per Group"]), type number}, {"GAExpTotal", each List.Sum([#"Total G&A Expense"]), type number}})
in
    #"Grouped Rows"

As you can see that the above records which are filtered total does not match the totals in the 2nd section.  How do I get the two tables to line up and display a running total?

[Sean]

@kfschaefer

Can you write on the image or somehow mark what fields you are referring to?

why do you have different field names in the 2 visualizations?

for example why Total Revenue and RevenueTotal? That should be the same?

what field do you want to have a running total for? You can do this with DAX 

Total Revenue = SUM (tablename[Revenue Column])

Running Total = CALCULATE ( [Total Revenue], FILTER ( ALL (tablename), tablename[Date Column] <= MAX(tablename[Date Column] ) ) )

Reminder: This is DAX so don't do this in the Query Editor

go to the table that contains Revenue column => go to Modeling Tab => click New Measure and type there

[kfschaefer]

Is there another way to display the total on the same visualization?, where it is display always at the bottom without scrolling?

[Sean]

If your table has items only in the Values - then the Total Row is fixed - even if you have scroll bars it will remain visible.

if you also have items in the Rows then you will have to scroll down.

No you can't change the location of the total row!

[kfschaefer]

Please explain this?  I am new to this?

"If your table has items only in the Values - then the Total Row is fixed - even if you have scroll bars it will remain visible."

My table has numerous values and I thought group on that table would get me the running total, however, this gets me a static value not changable by the slicers.

K

[Sean]

Never mind in a table all you have is Values. In a Matrix you can have Rows and Columns along with the Values.

bottomline your total row in a table should always be visible on the bottom row

with or without scroll bars it stays fixed and always visible

make sure it's turned on in the options - but it comes on by default anyway

as fas as the running total did you try my DAX formula

[kfschaefer]

sorry, I turned on the totals for the visualization and not totals are displayed.

Still unsure how to get the running totals to display on the visualization without using a separate visualizaton.

[Sean · Accepted solution]

@kfschaefer

Wow all you numbers must Data Type: Text - all seem to be left justified and that explains lack of totals!

Go to Modeling Tab and Check Data Type: for each Field in your table

[kfschaefer]

OK thanks for your time When I recreated the visual with the updated data the Totals now appear.  Thanks for putting up with me.

[Sean]

Remember this => left justified stuff in a table is NOT numbers

Numbers are always right justified in a table!

Its a helpful and easy way to see!

I bet now they are all on the other side - to the right!

[kfschaefer]

No, I already have them set to numbers either currency, percent or whole.

Still no totals,  in Query Edit mode or Model is there some where I am suppose to set to display Total Row?