Multiple Level of Detail Tables From One Dataset

jmmc · May 21

Hello, I have a product data set that has product sales with fields for store and city.

From this data set, I want to create three tables at each level of the hierarchy, product, store, and city.

The only way I can think of doing this is by creating a bunch of aggregate beastmode calculations for each field.

For example, if I want to view things at the store level, I'd place store name as the first column in a table, and for the address column, I'd create a beastmode: MAX(address).

This seems like a lot of work for this feature and would create a lot of calculated fields.

Is there a more elegant way of doing this? One challenge is that the global filters on the dashbaord should work with each level of detail/detail table.

Thank you in advance. You all have great insights.

MichelleH · May 22

@jmmc To add to @ArborRose's suggestion, note that you do not need to aggregate dimension fields like in your MAX(address) example. Table cards and pivot table cards will automatically aggregate the entire cards as long as every Metric (numeric) field is aggregated. You also do not need to create beast modes for simple aggregations like sums and averages, instead you can select an aggregation in the drop-down for the field, like below.

ArborRose · May 21

Based on your description, you want three tables with totals for each of Product, City, and Store.

https://embed.domo.com/embed/pages/D9npn

Each table card shown would be alike, with a different dimension in the first column.

There would be no beast mode calculations needed. And if each card uses the same dataset, you can have filters on it (shown) that control all the cards through selection.

But there are other ways to do this as well…pivot tables, etc.

These examples use this sample dataset as a csv…

Product	Store	City	Address	Sales
Product A	Store 1	City X	123 Street X	100
Product B	Store 1	City X	123 Street X	150
Product C	Store 1	City X	123 Street X	200
Product A	Store 2	City Y	456 Street Y	130
Product B	Store 2	City Y	456 Street Y	170
Product C	Store 2	City Y	456 Street Y	210
Product A	Store 3	City Z	789 Street Z	160
Product B	Store 3	City Z	789 Street Z	190

MichelleH · May 22

@jmmc To add to @ArborRose's suggestion, note that you do not need to aggregate dimension fields like in your MAX(address) example. Table cards and pivot table cards will automatically aggregate the entire cards as long as every Metric (numeric) field is aggregated. You also do not need to create beast modes for simple aggregations like sums and averages, instead you can select an aggregation in the drop-down for the field, like below.

jmmc · May 22

Thank you @ArborRose and @MichelleH - I think the key here was knowing that each unaggregated column in a table will automatically be part of the group by behind the scenes if measures are given a calculation.

Thank you!

Multiple Level of Detail Tables From One Dataset

Best Answer

Answers

Categories