How to Dynamically Change the Field in Matrix Rows Based on a Slicer?

[StevenHiatt]

I know it is possible to create a DAX formula that will dynamically change the calculated metric that shows in the values part of a matrix, but I'm wondering if it is possible to change the field that shows up in the rows?

As an example, I have a simple set of sales data (from the Adventure Works DB) that includes Date, Customer, Product, and Sales Amount. I would like to have a matrix that would show the total Sales Amount, but be able to change whether the rows are Customer or Product based on a slicer.

Desired output:

If Customer is selected in slicer - Customer shows in Rows

If Product is selected in slicer - Product shows in Rows

In my full data I have a number of metrics that I would like to be able to show for different groups (Business Unit, Sales Person, Customer, Product, Etc.). I know you can add them all in the rows field and drill down, but sometimes you want a different field to be on top. I'm trying to find the best way to show the same matrix for multiple categories without having multiple tabs. Is this possible or just wishful thinking?

For the screenshots above I used some data from the AdventureWorks database. Here is a link to the pbix on Dropbox:

Dropbox file - Sales Sample.pbix 

[Greg_Deckler · Accepted solution]

@StevenHiatt Maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-EVERYTHING-measures-axis-legend-titl...

[StevenHiatt]

@Greg_Deckler That link has some good information on how to dynamically change the values and a title, but I'm not seeing how I can use that info to change the rows in a matrix. Any other help?

[Greg_Deckler]

@StevenHiatt It *should* be the same concept as changing the Axis in the column chart. Basically, you are going to need a table that has all of your customers and products in a column with your categorization (slicer) in another column in the same table. Then, when you select the item in the slicer, only the rows (either product or customers) is shown in the rows of the matrix.

[StevenHiatt]

@Greg_Deckler Thanks. I looked a bit deeper at the example in the link and was able to understand the concept. I've now added a new table with the category and list of customers/products. I also added an index column to the original table and pulled that into the new table as well and added the relationship. So the rows in the matrix will now change when I select the different categories in the slicer. This is awesome!

The issue I have now is that all the values are giving the total of everything and not just the total of the customer/product shown.

The example file in the link has DAX formulas that return diffierent metrics based on the filter selections, but in my case it is just the one field Total Sales. I've been trying to get it work but haven't been successful yet. What am I still missing?

Here is a link to the pbix:

Dropbox File - Sales Sample 2.pbix