cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
fazad Frequent Visitor
Frequent Visitor

Filtering certain columns out from matrix view

Hi All,

 

I'm having an issue with regards to the matrix functionality on power BI.

 

I have imported the following work book from excel.

 

bi1.PNG

 

When I generate the matrix in power BI, I'm getting blanks for projected sales for the first 2 years. I do not want to show those columns and only want to show the projected sale column for 2019 year only. Any idea how I can remove those views from matrix? If I try to use the exclude button and try to exclude project sales only, it removes the entire year and total sales cloumn as well.

 

bi2.PNG

 

Any advice or help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filtering certain columns out from matrix view

Hi @fazad,

 

For your requirement, you could create a calculated column to achieve your desired output.

 

Column =
VAR sum_of_project_sales =
    CALCULATE (
        SUM ( 'Table1'[Projected Sales] ),
        ALLEXCEPT ( Table1, Table1[Year] )
    )
RETURN
    IF ( ISBLANK ( sum_of_project_sales ), 0, 1 )

Then you could drag the calculated column to visual level filter and set like below.

 

Untitled.png

 

 

More details, please refer to the attachement.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Filtering certain columns out from matrix view

Hi @fazad,

 

For your requirement, you could create a calculated column to achieve your desired output.

 

Column =
VAR sum_of_project_sales =
    CALCULATE (
        SUM ( 'Table1'[Projected Sales] ),
        ALLEXCEPT ( Table1, Table1[Year] )
    )
RETURN
    IF ( ISBLANK ( sum_of_project_sales ), 0, 1 )

Then you could drag the calculated column to visual level filter and set like below.

 

Untitled.png

 

 

More details, please refer to the attachement.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
fazad Frequent Visitor
Frequent Visitor

Re: Filtering certain columns out from matrix view

Hi Cherry, Apologies for the late reply, your advice worked, thanks!

Super User
Super User

Re: Filtering certain columns out from matrix view

Hi,

 

You may refer to my solution in this PBI file.  My numbers are different from yours because i created a summy dataset.

 

Hope this helps.

 

Untitled.png