Multiple Level of Detail Tables From One Dataset

Options

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.

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @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.

Answers

  • ArborRose
    ArborRose Coach
    edited May 21
    Options

    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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @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
    jmmc Member
    Options

    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!