Forum Discussion
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 |
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
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.
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.
4 Replies
- parry2k
Super User
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-xicaiCommunity Support
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.
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.
