Skip to main content
cancel
Showing results for 
Search instead 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

Reply
tfiegel037
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:

RegionSales
Eastern300000
Western340000
Central220000
Southern130000
Northern450000

 

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
DataInsights
Super User
Super User

@tfiegel037,

 

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

 

DataInsights_0-1720647172809.png

 

DataInsights_1-1720647191643.png

 

DataInsights_2-1720647229040.png

 

Measure:

 

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

 

DataInsights_3-1720647289857.png

 

DataInsights_4-1720647306150.png

 

DataInsights_5-1720647319706.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
DataInsights
Super User
Super User

@tfiegel037,

 

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

 

DataInsights_0-1720647172809.png

 

DataInsights_1-1720647191643.png

 

DataInsights_2-1720647229040.png

 

Measure:

 

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

 

DataInsights_3-1720647289857.png

 

DataInsights_4-1720647306150.png

 

DataInsights_5-1720647319706.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors