Filtering certain columns out from matrix view

[Anonymous]

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.

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.

Any advice or help would be appreciated.

[Ashish_Mathur]

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.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

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.

More details, please refer to the attachement.

Best  Regards,

Cherry

[Anonymous]

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