Adding a 'Total' row based on the currently displayed (filtered) data in a table

[Tomburton2023]

 Hi

I am trying to work out how to do the above and struggling - it feels like I'm missing something obvious! Any help or advice would be appreciated.

I have a report which pulls data from two SharePoint lists. One of the columns in one of the lists has a 'Total WIP' number column, see below for how this looks:

The user of the report can filter by other columns using slicers (such as to filter the Month, Year etc).

I need to add a Total to the 'TotalWIPForTable' column which will display a sum total value for all the currently displayed values (based on what the user has filtered in the table). I can't work out how to do this - if I add a regular Total row, it displays a sum total of all rows/records from the table, but doesn't respect the filter.

Thanks in advance for any help.

Cheers

Tom

[Dezmond95 · Accepted solution]

So i'm guessing your table is built a little bit like this one where most the values are coming from the Matters table but you also have the month and year from the month value table?

My understanding is that when you attempt to select a month in the slicer, it will change the table visual and appear to work by basically only displaying the selected month rows. However, because the month filter cannot go against the relationship direction, your matters table will still be a complete unfiltered table in the background. Since your values for Total Billed WIP etc are held there, when it goes to compute the total, the entire table is being summed as the table hasnt been able to be filtered to just the selected month.

The below table shows what I think is happening in your case where it is only showing the matching months. For the red columns, these are more like the visible rows rather than filtered rows. The table is still complete but your only being shown these ones. The total is still summing the non visible rows as they are still there.

For the solution I hope. There is a function called Related. It allows you to pull the related value from the ONE side of the relationship into a column in the many side by specifying the column you want to pull the value of . The table below is the month values table, I created new columns and added in Fee Earners, I repeated the same process for Person in Charge, and Total Billed WIP.

After this I recreated the table now that I can use columns from just my Month Values Table. I updated the slicers to use the new columns in the Month Values table.

 This is using the Person in Charge (From the related function) as a slicer, the total is appearing correctly.

 i tested it with a combination of the slicer options you wanted.

It all appeared to work. 

Hopefully this solution works for you.

It's likely not the best performance wise but it should hopefully allow you to function as you want it too.

Let me know if you need more advice.

P.S. I would recommend blanking out any personal names or details in your pictures when posting.

[Tomburton2023]

@Dezmond95  - thanks again for your help, I've followed the steps you took and it has worked 🙂

Thanks ever so much for the assistance.

[Tomburton2023]

@Dezmond95 many thanks for this - I will try this out as soon as possible!

[Dezmond95]

Maybe something along the lines of 

Measure name =

SelectedValue(

        Sum(totalwipcolumn),

        Calculate(

             Sum(totalwipcolumn),

             Allselected(tablename)

       )

)

In other words, if there is one value selected, perform the basic sum measure, otherwise perform the sum over Allselected items in the table (that should hopefully take the filter context into account). This way for each row in the table there will be one item selected but for the total it will for multiple so it will display the total for all in that context. 

[Tomburton2023]

@Dezmond95  thanks for this - I've tried setting up a new measure. Almost there, but seeing an error on it. Any suggestions?

Thanks

Tom

[Dezmond95]

Sorry, I got that wrong, the selected value won't work as it's used to return the value of the single category chosen. Perhaps something like this could work

If(

    HasOneValue(column name),

    Sum ( totalwipcolumn),

    Calculate(

        Sum(totalwipcolumn)

        Allselected (corporate wip sheet              table)

)

Once I'm at my computer, I can have a proper look. I'd even try just the calculate part and eliminate the selected value part. 

Edit: Could you provide an image of your model view. Alternatively specify the relationships you have between your tables (and if one to many, many to many, one direction or bi-directional). Then specify what table[columns] are used in the table, then what table[columns] are being used to filter/ used as slicer options.

[Tomburton2023]

@Dezmond95  thanks again for your help. I tried the above and sadly couldn't get it to work.

When trying your formula above as it is, the measure column showed a total value for each row, but the total figure at the bottom was still a static value showing the total for all records in the table, and didn't respect the slicer/filter (which is what I need it to do).

When I tried your formula but without the 'selected value' part, the total value for each row as as well as the total figure at the bottom was just the total for all records in the table.

Here's an image of my model view:

As the screenshots hopefully show, there is a 'one to many' relationship between items in the parent table (Corp WIP Sheet Matters), and items in the child table (Corp WIP Sheet Month Values), where the ID is the link (so one Matter can have one or more Month values).

My slicers use the following columns:

- 'Year' and 'Month' from the Corp WIP Sheet Month values table

- 'Fee Earner' and 'Person in Charge' from the Corp WIP Sheet Matters table

The thing I'm essentially wanting to do is - give the user the ability to filter the table in the report so that it shows all Matters which have at least one linked Month value in the selected month and year (via the slicer). Each Matter has a 'Total WIP', 'Total Billed WIP' and 'Total Unbilled WIP' value.

I want a total measure/row to show the total value, for those three total columns, based on the currently displayed (filtered) records. This is the bit I just can't seem to get working.

The 'all selected' aspect of the DAX seems to represent what is selected in the table visual, not what is selected in the slicer. It's as if I need some way of referring to the slicer name/chosen value itself in the DAX.

Thanks in advance for any further help you can provide.

Tom

[Dezmond95]

Thanks for the extra information.

The first problem I've identified is that you are using the Year & Month columns in the Month column to try filter the Matters table. The reason this won't work is because of the relationship direction. The arrow is pointing towards the month value table which means you cannot filter the matters table by a column in the month value table.

I'm thinking you could try to merge the tables in Power Query based on the columns you are using for the relationship once you have the date columns in the Matters table, you can add a date table and apply a filter to this table.

I can look into it further tomorrow though

[Tomburton2023]

@Dezmond95 thanks for your help.

Excuse my ignorance here - would the inability to get the total row working be based on the above? The reason I ask is - I can use the slicers/filters successfully to filter the table visual, the filtering does work. It's just the total row doesn't respect the filter.

I will have a look at Power Query as you've mentioned.

Cheers

Tom