Filter by Category Sum in Metrix using Slicer

[Anonymous]

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)

[Anonymous]

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]

Hi @Anonymous ,

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.