Table visual - add an unfiltered totals row

[Dog]

Hi All, 

I have a client who would like to display a row within the table that totals the items that have been filtered out. 

so if I have 20 rows of data displaying sales by product and the end user selects a value from a slicer to filter this down, they would like to see the items that have been removed consolidated under one row - thus maintaining the same grand total. 

Has anyone ever done anything like this before? 

Thanks

Dog

[Pavlous]

If I understand this correctly, you want to keep filtering data and by the filter, they will be changing. But the data filtered out will be displayed somewhere under one row? Or you want to sum all the filtered out data into one row?

[Dog]

Hi @Pavlous

yes you are correct, I want the filtered out data to appear under one row within the table 

so basic table unfiltered would look like this. 

then if i filter by "Car" then it would look like this. 

this is obviously a very basic view of what I am trying to achieve but the concept is the same. 

Thanks

Dog

[v-ljerr-msft]

Hi @Dog,

While there is no direct way to do it currently, an alternative way is to use the formulas below to create two measures to calculate 'Total Sales' and 'Other Sales'.

Total = CALCULATE(SUM(Table1[Sales]),ALL(Table1))

Others = [Total] - SUM(Table1[Sales])

Then show the two measures on Multi-row Card visual, and put the Card visual under/align your Table visual like below. 

Regards

[Pavlous]

Well the thing is, that you cant made dynamicall dependencies on what you will select on filter visual. And also you would need some DAX formula which would cooperate with this dependencies so it can resolve what you are filtering and what to do next.

I do not think, that this is possible to do.

[sdjensen]

If it's just to keep the totals then you could perhaps create another table visual with the same measures and use visual interactions to disable filters from affecting this new visual - this way you will keep the grand total.