Forum Discussion
How to Dynamically Change the Field in Matrix Rows Based on a Slicer?
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
4 Replies
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?
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.
