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
AbhinavM
New Member

Filter by Category Sum in Metrix using Slicer

Hi People, 

Hope you are doing well.  I am actually struggling with an issue where - I am using a matrix build by measures. 

Using two columns in ROW as Category and sub-category. In VALUES field all values are coming from Measures.

Now I want to filter the matrix by Category Total using a Slicer.  (apologies cannot share client data )

e.g.

Category              Sales (sum of sales is coming from measure)

Electronics            100 (Total)  

1.1)Fan                   40  

1.2)Bulb                 40  

1.3)Switch              20

Furniture               80 (Total)  

2.1)Chair               30  

2.2)Table               30  

2.3)Stool               20

Now when I apply a slicer i.e. Less than 90 then only Furniture and its subcategories should display. whereas Fan, Bulb and Switch in Electronics category has total less than 90 but since total of electronics is more than 90 it should not appear.  I am already obliged to be able to send you this.  Thanks a lot in advance.  Abhinav (India)

2 REPLIES 2
AbhinavM
New Member

Hey @davehus,
Thanks for heling. 

I have tried that, using AllExcept is not solving the purpose fully, it is letting me to sum the total by category but when I am using that as a column in Metrix all other filters/slicer on page  are not applying properly. 

davehus
Memorable Member
Memorable Member

Hi @AbhinavM ,

 

Not sure what your underyling date is like, but you could created a calculated column such as

 

Calculate(Value, AllExcept(Table, Table[Department])) You can then use this column to filter to your requirements. The only caveat is that it will not be dynamic as it runs once at refresh.

 

Hope this helps.

 

Did I help you today? Please accept my solution and hit the Kudos button.

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors