Custom sort column variables in matrix table visual based on a selection

[PVS07]

Hi All,

I am new to Power BI and self learning. I am trying to replicate a table that I have build in Excel. The table has 4 column geographic variables - Suburb, Area, Division and State. I have set it up in Excel with a dropdown for Suburb. When you select a desired Suburb then the Area, Division and State should auto update (I used Vlookup in Excel to achieve this). Please see the screenshot of the Excel table below for detail.  My geographies distribution - 78 Suburbs, 17 Areas, 4 Divisions and 1 State. 

I have tried to put all geographies in one column but when I created a Matrix visual in Power BI it is listing all the geographies, and I am not sure how to filter/sort it. I also tried creating four different tables for each geography and tried to pull the data into one Matrix visual but that also did not work. 

Can someone please let me know if it is possible to achieve and how. Any help is greatly appreciated.

Thanks in advance.

[amitchandak]

@PVS07 , Not very clear. Have tried it on power BI. What the challenge you faced.

What is the list of value in the screenshot

[PVS07]

Hi amitchandak,  thanks for the quick response. 

I will try to explain a bit further. I got four levels/hierarchy of geography - Suburbs > Areas > Divisions > State. I want to list the values of each level of geography in the columns field, i.e. %, LL (lower confidence interval) & UL (upper confidence interval) for each of the 4 geographies as per the screenshot in my original post.  My data has 78 Suburbs that are distributed across 17 Areas, 4 Divisions and 1 state. Once I create a table in Power BI I would like to include a slicer for Suburbs. The idea is, once I select a Suburb the selected Suburb with the corresponding Area, Division & State data should populate in the table. 

I was able to do this in Microsoft Excel and not able to figure out how to format my data in Power BI to get the desired outcome. I have created a sample Excel table and Power BI file with sample data. Please see them in the following link https://1drv.ms/u/s!AubIV2PXG9p4gqcsKpOzz2koutbbUA?e=BjuIjz 

I hope the sample files will help in getting a better understanding. 

Thank you.

[mwegener · Accepted solution]

Hi @PVS07 ,

take a look at the attached PBIX.

[PVS07]

Hi @mwegener,

Greatly appreciate your response and thanks a ton for the solution. Your method worked like a charm. Very clever. You are amazing. 

You have saved me a lot of time. I worked out a way which was very clunky and a lot of repetition of the data which was not ideal. 

 I can't thank you enough for your help. Stay safe. 

Regards.