Dynamic Grouping of aggregated data

[prichter_mk]

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

2. Group by VOLUME

STORE COUNT	VOLUME	SALES	FORECAST
3	HIGH	350	440
2	LOW	50	60

3. 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!

P

[parry2k · Accepted solution]

@prichter_mk I would recommend to use bookmark to achieve this, basically you create bookmarks to show and hide different visuals and then on click of button call the respective bookmark.

[v-xicai · Accepted solution]

Hi @prichter_mk ,

As parry2k's suggestion, you may create bookmarks for every selected options.

For references:

Use bookmarks to share insights and build stories in Power BI ,

Use bookmarks in Power BI Desktop to share insights and build stories .

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[prichter_mk]

@v-xicai @parry2k Thanks for the quick reply. I will research the use of bookmarks further, however, the actual scenario that I'm dealing with contains approximately 10 different "group by" selections, not just 2 like I provided in the example. Although possible, it's going to be difficult to create/manage/present a bookmark for each possible selection permutation. 

In addition, there will be multiple filtering slicers that will be selected by the user (for example, SKU #) before the data is ready to be grouped by one of the many optional column groupings. I was hoping there would be a way to dynamically choose which optional column groupings to group by via some mechanism resembling a slicer list. The user workflow does not always dictate that the same column grouping selections will be made for each filtered data set. 

[v-xicai]

Hi @prichter_mk ,

It is unavailable to get some mechanism resembling a slicer list to control columns of table visual as you desired, you might post your new idea here https://ideas.powerbi.com/forums/265200-power-bi-ideas , and add your comments there to improve Power BI and make this feature coming sooner.

As a workaround, you might try the bookmark or create 10 table visuals for every group.

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k · Accepted solution]

@prichter_mk I would recommend to use bookmark to achieve this, basically you create bookmarks to show and hide different visuals and then on click of button call the respective bookmark.