Dynamic Grouping of Aggregated Data

Hello all,

I have a requirement to apply dynamic column grouping to aggregated data.

The underlying dataset is a table that contains STORE NUMBER, REGION, VOLUME, SALES, FORECAST.

STORE NUMBER REGION VOLUME SALES FORECAST
1 A HIGH 100 120
2 A LOW 20 20
3 B LOW 30 40
4 C HIGH 120 180
5 C HIGH 130 140

The initial presentation of the data will be as follows. The SALES and FORECAST data will be summed across the entire dataset with a count of total stores.

STORE COUNT SALES FORECAST
5 400 500

The users would then like to be apply dynamic grouping of the aggregated dataset without filtering out any of the underlying dataset. In this example, they’d want the options of grouping by data by (1) REGION, (2) VOLUME, or (3) REGION and VOLUME.

  1. Group by REGION
STORE COUNT REGION SALES FORECAST
2 A 120 140
1 B 30 40
2 C 250 320
  1. Group by VOLUME
STORE COUNT VOLUME SALES FORECAST
3 HIGH 350 440
2 LOW 50 60
  1. Group by both REGION and VOLUME
STORE COUNT REGION VOLUME SALES FORECAST
1 A HIGH 100 120
1 A LOW 20 20
1 B LOW 30 40
2 C HIGH 250 320

I’ve attempted to use slicers for the selection of the groupings but that ends up filtering per selection as one would expect. Any help or direction on how to support the desired functionality would be much appreciated.

Thanks!

Hi @_mk7, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi @_mk7,

See if this meets your requirement.

On the left of the page all separate aggregate tables are visible as illustrated in your post.
On the right, you can right-click a button (in desktop, in the Service a click will do) to be shown the selected aggregate table.

image

This is based on the use of Buttons, Bookmarks and the Selection Pane to show/hide visuals.
Here’s a sample PBIX. eDNA - Dynamic Grouping Aggregated Data.pbix (23.3 KB)

I hope this is helpful.

1 Like

Thanks for the solution, Melissa! This definitely seems feasible for the 2 column example I provided.

The requirement I’m trying to satisfy actually calls for 8 different columns to be “dynamically grouped”. One to many of the columns could be selected in any combination. In order to support all permutations, it seems somewhat unreasonable to have 256 different bookmarks corresponding to each distinct selection possibility.

No argument here!

You could also explore the Personalize Visuals option (with perspectives, see documentation).

2 Likes