Matrix Table Visualization

[jafdias]

Hello Guys.

I have a file and i'd like to now if can i do something like this.

Currently i have a file in MS Excel, and bring some data from a pivot table  (Getpivotdata). I've been transfering this information to power BI.

My information i MS Excel look like a picture bellow.

First Column is sum of Fiscal Year 17.

Seconf Column is sum of Fiscal Year 18.

and the other columns is FY18, but per quarter.

In the PowerBI, loaded the database, but i could hide the others columns of Quarter for others fiscal year diferent from FY18 like picture below.

it is possible do in PBI something like the first picture?

I have attached a PBIX file with database and this table.

https://app.powerbi.com/groups/me/reports/8e032471-8f2e-46e0-9e78-b0cd45a58f4b?ctid=ca7981a2-785a-46...

Thanks.

[Seward12533]

I could not access your report, can you save it dropbox, googledocs or something and share. That is one of the limitaions of the visuals PowerBI.  You cannot selectively hide or colaps grouping.  But you can block them from displaying with a measure.

You would need to test to see if the current filter context ISFILTERED by Quarters and if it is only calculate result if the year = current year. 

Assuming you have date table that you are getting year and quarter from and you have mesure already written to get the result you displaying you can write a new measure to use in your chart. 

Dispaly Measure = IF(ISFILTERED(date[QTR]),  //Test to see if filtered by quarter
                                   IF(YEAR(MAX(Date[Date])=YEAR(now()), [Measure] // If year is current year then display mesure,

                                        blank())  // ELSE return NULL so value is not shown

                                  ,[Measure]) // IF Not filtered by quarter display the measure

[jafdias]

Hi Seward, thank you for your message of answer. I was on vacation and returned today.

the file was uploaded in my Google Drive.

https://drive.google.com/open?id=1-Oxe8b_Aqeqx7Bj5oQA5QdUlDQJWowCm

I tried  your function, but i think you can see better in the file the information that i'm talking about.

Thank you again for you help

[Seward12533]

I'll try to take a look at it later. My company blocks google docs so I can't check now.  Potential good news.  I attended the recent BI Summit in one of the released in the next few months (Oct Most likely but perhaps earlier) MS is planning on releasing an update that will allow collaps of individual nested columns or rows in a Matrix Visual that will allow you to get the results you want. Not sure if you will be able to do it wiht Column or Bar Charts but it will be one step closer. For now my soluiton is to deploy an Excel Workbook with a PowerPivot Model so I can build reports similar to what your trying to do.

Another approach that might meet your needs is to use Report Tool Tips feature that was recently released that will allow you to have a chart with details by month when you over over a bar for the Year, Half, Quarter or whatever. See this tutorial - https://www.youtube.com/watch?v=URTA7JZsAtw