Forum Discussion
Create a running total that changes on filtered records
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?
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
10 Replies
- Sean
Community Champion
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
Helper IV
Is there another way to display the total on the same visualization?, where it is display always at the bottom without scrolling?
- Sean
Community Champion
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!