SRSS/PowerBi Report Builder - Add Category Total to Bar Chart

Question

I've built a report that shows a value for each region (Asia, Americas, EMEA) on a bar chart. I would like to have a fourth bar on the same chart that shows the overall value for all 3 regions.

I've been playing around with the category groups settings of the chart to understand if I could add a "group by" expression that would select all field values, but it doesn't seem to work.

I read elsewhere that I can use UNION but I'm not sure where to use that in PowerBI report builder? Is that adding a new dataset with a custom query? I don't think I can show values from a different dataset in my current chart.

I've avoided the problem for now by adding an extra chart just for the aggregate, but it's far from ideal.

Any help would be greatly appreciated!

Tried to create a calculated field, or to play with the category group settings, or adding a new category group called "All Regions" but that didn't work.

Answer 1

It depends on how you get your data in the dataset query.

If it's was a simple SQL query such as SELECT Region, SUM(Value) AS Value From myTable GROUP BY Region

then you could UNION the aggregate to this with

SELECT Region, SUM(Value) AS Value From myTable GROUP BY Region 
UNION ALL 
SELECT  'All Regions' as Region, SUM(Value) AS Value From myTable