Getting Selected Columns for Matrix Visual

[Arshadjehan]

Hi,

     I am trying to Display year wise budgetary columns in matrix visual, but NOT all columns from every year.

Currently I am getting 6 columns for each year at different categories (rows) level as below:

But I dont want every column for every year. Like my requirement is to display :

•       All 6 columns for Year 2019
•       Only "Actual" columns for 2016, 2017 and 2018
•      Average of "Actual" columns for 2016, 2017 and 2018.

Any help will be highly appreciated....

[v-lili6-msft]

hi @Arshadjehan 

You could try this way as below:
Step1:

Add a dim categories table as below:

Dim categories = UNION(VALUES('Table'[categories]),ROW("categories","Average of Actual"))

Then create a relationship with basic fact table

Step2:

Create a measrue as below logic:

Measure =

IF (

    SELECTEDVALUE ( 'Dim categories'[categories] ) = "Actual",

    SUM ( 'Table'[Value] ),

    IF (

        SELECTEDVALUE ( 'Table'[Year] ) = 2019,

        SUM ( 'Table'[Value] ),

        IF (

            SELECTEDVALUE ( 'Dim categories'[categories] ) = "Average of Actual",

            CALCULATE(AVERAGE('Table'[Value]),ALL('Table'[Type]),FILTER(ALL('Dim categories'),'Dim categories'[categories]="Actual"))

        )

    )

)

or

Measure 2 =

IF (

    SELECTEDVALUE ( 'Dim categories'[categories] ) = "Actual",

    SUM ( 'Table'[Value] ),

    IF (

        SELECTEDVALUE ( 'Table'[Year] ) = 2019,

        SUM ( 'Table'[Value] ),

        IF (

            SELECTEDVALUE ( 'Dim categories'[categories] ) = "Average of Actual",

            CALCULATE(AVERAGE('Table'[Value]),ALL('Table'[Year]),FILTER(ALL('Dim categories'),'Dim categories'[categories]="Actual"))

        )

    )

)

Result:

And here is sample pbix file, please try it.

Regards,

Lin

[Arshadjehan]

Thanks @v-lili6-msft for detailed answer.

But "Categories" you used in sample fact table as ROWS, are actualy columns in my table. 

Here is the sample snapshot;

How can I refer to these columns, and get the similar solution you gave, withing the matrix I pointed to in my first post?

[v-lili6-msft · Accepted solution]

hi @Arshadjehan 

If so, you could only try these two workaround:

1. Unpivot the these six columns and then get it as above.

https://radacad.com/pivot-and-unpivot-with-power-bi

2. disable the "Word wrap" in Format-> Column headers of matrix, then reduce the column width to 0 manually for year 2016/2017/2018.

Regards,

Lin

[az38]

Hi @Arshadjehan 

there is no such option for matrix visual. you can hide columns only the same for each year

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Arshadjehan]

Hi @az38 

Thanks, but how can I hide certain columns for one year and display all columns for other?

[az38]

@Arshadjehan 

I dont see any chance to do it

do not hesitate to give a kudo to useful posts and mark solutions as solution