Filtering a matrix in Power BI but not the rows

Question

I want to filter a matrix of data in Power BI by total for one category but leave the row context unfiltered to allow drill down.

I am using a switch measure but it evaluates down to the row level too and I'm not sure how to adjust it. Can someone provide a suggestion?

This image shows filter of >100000 but the filter is applied to rows too

Swtich = SWITCH(SELECTEDVALUE('Sales Control Table'[ID]), 1,[Over25000],2,[Over50000],3,[Over100000],[Total Sales])
Over100000 = CALCULATE(SUM(Sales[Net Sales]),filter(Sales,SUM(Sales[Net Sales]) > 100000))

Also I tried using visual filters but that doesnt help me

Falcon151
– Falcon151

2022-03-10 20:49:30 +00:00
Commented Mar 10, 2022 at 20:49 — Falcon151
– Falcon151, Commented Mar 10, 2022 at 20:49

Falcon151 · Accepted Answer · 2022-03-11 16:01:39Z

0

Created a filter, thanks guys I learned a lot

sumofitem = CALCULATE('_Sales Measures'[Swtich], ALLEXCEPT(Sales,Sales[Item]))

SelectedFilter = IF(ISBLANK('_Sales Measures'[Swtich]) || ISBLANK('_Sales Measures'[sumofitem]),1,0)

answered Mar 11, 2022 at 16:01

Falcon151

12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Tyler2P · Accepted Answer · 2022-03-11 19:18:43Z

0

I was able to modify my code to show blank for the top hierarchy, now I just need to filter it out

Over100000 = if ( ISINSCOPE(Sales[Cust Category]), (CALCULATE(SUMX(sales,Sales[Net Sales]), filter(Sales, (Sales[Sold To Name] in DISTINCT(Sales[Sold To Name]))))) ,  ( CALCULATE(SUM(Sales[Net Sales]),FILTER(Sales, SUM(Sales[Net Sales]) > 100000))))

edited Mar 11, 2022 at 19:18

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered Mar 11, 2022 at 15:14

Falcon151

12 bronze badges

Collectives™ on Stack Overflow

Filtering a matrix in Power BI but not the rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related