Show Aggregated Sum of Column Values

[Peter_S1]

Hi,

I would like to show an aggregated sum of values from a column. Let’s say it’s a Sales column and I would like to show a high level sum of total sales that are in the Sales column and link it to a dashboard.

I’ve created a slice that only has the Sales column in it. Then, I created a view, but not able to get the aggregated sum. In the table view type there is an aggregation option where I chose Sum of Sales, but it still lists out each row. I like how the detail view type looks since it only gives a clean number, but this only gives one line item and does not aggregated.

Could someone please help with this?

[WillowMobileSys]

Peter_S1:

In the table view type there is an aggregation option where I chose Sum of Sales, but it still lists out each row.

There is one trick you can use in a Grouped Table view to get it to show in a collapsed mode. Apply a second grouping level. This will show just the Groupings with the aggregated value. An advantage is that you can expand the Group to see the list of rows.

Peter_S1:

I like how the detail view type looks since it only gives a clean number, but this only gives one line item and does not aggregated.

If the Table view option is out, there are a couple options to use in a Detail view.

1. If you only need 1,2 or so aggregated values, create a Virtual Column on the row that will compute the Aggregated Sum. This will be on every row so you could show it along with the Detail view of each row. OR you could create a custom view that ONLY shows the Aggregated amount along with any other reasonable attributes. How you reach this view will be up to you and your preference in the app. NOTE: To make sure you do not interfere with the original Detail view, you may need a Slice (as you have already done) and create a view on that Slice. The Slice would still be ALL rows but only the necessary columns.

2. If you are going to have many Aggregated values to show, (i.e. a summary report view) then I would advocate for a dedicated table of the computed aggregated values with its own views. To be the MOST user friendly I would suggest to update the Summary table as the Detail rows are updated. This allows users to see the Summary data as it changes (as opposed to running a process once a month but sometimes this is all thats needed). This option is the most work but provides the best results (for users AND in app performance) when quick and constant access to this Summary data is needed.

[Peter_S1]

Thank you, I will go through your suggestions and see if I can make them work. Appreciate your help.

[Peter_S1]

I have created a virtual column as you’ve suggested in point 1 above. The issue I’m still running into is that the Key column has to be included in the slice and so then the aggregation is grouped by the Key. Is there a way to display the pure sum of the column?

[WillowMobileSys]

Peter_S1:

Is there a way to display the pure sum of the column?

Yes. I think you may have a wrong impression of what a Slice does for you. A slice should be treated just like a table - it just has fewer rows, fewer columns or both. No matter how many columns you have you will need to use a List-type expression, such as SELECT, to retrieve the values and operate on them.

In your case, you have a column in your Slice you wish to be summed - [Column to be summed].

So the expression would look like this:

SUM(SELECT(Slice Name[Column to be Summed], TRUE))

If you needed to further filter the slice rows, then replace TRUE with the filter criteria.

If no need to filter further then you can use this short-hand expression:

SUM(Slice Name[Column to be Summed])

The short-hand expression will simply provide the summed result for all rows in the slice.