Forum Discussion

kfschaefer's avatar
kfschaefer
Icon for Helper IV rankHelper IV
10 years ago
Solved

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?

  • Sean's avatar
    Sean
    10 years ago

    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

     

10 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion

    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's avatar
      kfschaefer
      Icon for Helper IV rankHelper IV

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

      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity 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!