Databricks Dashboards - display totals in Tables

[Diegol1337]

I was wondering if anyone has been able to display totals in a Table/Pivot visualization? Similar to what is possible in other BI tools?

If I have a table with a bunch of rows and one of the columns is Total Cost, I want to be able to show a Total at the end of the table which sums the total cost of the column. Would also like for the Total to be flexible and always displayed.

If possible would like to avoid calculating the Total in a SQL query, any ideas

[bianca_unifeye]

At the moment, Databricks visualizations do not natively support dynamic total rows or grand totals like Excel or Power BI do.
You can show aggregations (SUM, AVG, etc.) in your query, but the visualization layer itself won’t automatically display a “Total” row at the bottom.

If you build dashboards through notebooks or SQL queries, you can use a DataFrame summary and render via display(). Simpler way, it is still sql, use UNION ALL. You can always built a custom visual.

Databricks has this feature on the roadmap for Lakeview Table visualizations, expected to support total and subtotal rows similar to pivot tables soon, but I don't know the release date!

[OndrejHavlicek]

Totals are possible in pivot tables now, for both row and column groups. The option "Display total" is a bit hidden in the group properties.

[SFDataEng]

In a Pivot Table, "Display total" is checked for the Row, a row for Grand Total gets added, and the layout looks correct, and there are values in each column for Grand Total, but the totals are wrong -- the Grand Total values are just repeats the values from the first row of the pivot table.  I tried refreshing the dataset.

I am very grateful for the existence of "Display totals" feature, but I wonder if it needs to be bugfixed or documented.

Since pivot table totals are a new feature, I comment in hopes that this helps iron out the functionality.

[OndrejHavlicek]

@SFDataEng For me the totals work correctly. Please double-check your data, metric, and chosen aggregation. You can perhaps best sanity-check it using some simple metric like count(*) and SUM aggregation.

[SFDataEng]

Request: option for "Total (Sum)" row for Pivot Tables

Hi @OndrejHavlicek, your statement and implication appear correct, if I understand accurately.

Clarification

The "Grand Total" line added by the "Display total" checkbox uses the same aggregate function as used for the individual rows in that column, but across all underlying data for that column, to get the Total-aggregate-result, called total (in widget config pane) and Grand Total (in row label).

Source of confusion identified

I had been anticipating that the "Total" row added would always perform a "Sum" of the (potentially already aggregated) values above it.

• For context, this anticipation that "Display Total" would result in a "Total (Sum)" row matches the Pivot Table's optional "Summary" row functionality in Apache Superset dashboards.
• This "summing of aggregates to arrive at a total", in order to summarize aggregates of a type other than SUM(), needs to be used carefully, but is valid in some data shapes and use cases.

Conclusion

My comment was related to the OP's topic that

---

@Diegol1337 wrote:

...I want to be able to show a Total at the end of the table which sums the total ... of the column. Would also like for the Total to be flexible and always displayed....

---

Kind request

Given this clarity:

• I am very grateful for the existing "Total / Grand Total" option that has been put in place, which appears to be a standard Pivot Table Total implementation.
• I am very much hopeful and requesting for a second "Total (Sum) / Summary" option to be added to Pivot Tables, which would always Sum the values appearing immediately above it (even summing the aggregates arrived at by another function).

Image 

This is a contrived example, but we could get into what data shapes would make this valid, and why they are useful.  Hopefully, that is not needed.  In my current view, as a refinement from the image below, probably the "Display total" checkbox and optional "Grand Total" row can stay as they are as a feature; but "Total (Sum)" would be a good name for the distinct feature concept I was looking for (and, for a moment, thought I had found in this discussion!).