cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Create a slicer that also combines multiple selections

I have a data set that contains five regions, but I want to create a slicer that lists seven items: the five individual regions, a combined region, and then all regions. I don't just want to use groups as I need to filter on individual selections as well.

This is a project to convert an existing excel report which utilizes multiple IF(AND(OR))) calcs to achieve the result.  For clarity, I am an advanced Tableau user, so I know how to do this in that tool, but our company uses PBI, and I don't know how this translates over.  In theory I know how it should work, I just can't figure out how to accomplish the task.

For example, using a simple dataset:

 Region Sales Eastern 300000 Western 340000 Central 220000 Southern 130000 Northern 450000

I want to be able to create a slicer that not only has these five regions, but also a NorthEast region, as well as an All Regions.

I know you can multi-select within a slicer, but I want the slicer to show all regions, as well as the combined region in a single selection.

Slicer:

All Regions

Eastern

Western

Central

Southern

Northern

NorthEast

Results being that selecting All Regions would show 1440000, any specific region would show their resepective sales amount, and NorthEast would show 750000.

1 ACCEPTED SOLUTION
Super User

This solution uses the two tables below. There are various ways to create them (manually, DAX, Power Query).

Measure:

``````Total Sales =
CALCULATE (
SUM ( FactTable[Sales] ),
CROSSFILTER ( RegionGroups[Region], FactTable[Region], BOTH )
)``````

Proud to be a Super User!

Super User

This solution uses the two tables below. There are various ways to create them (manually, DAX, Power Query).

Measure:

``````Total Sales =
CALCULATE (
SUM ( FactTable[Sales] ),
CROSSFILTER ( RegionGroups[Region], FactTable[Region], BOTH )
)``````

Proud to be a Super User!