Forum Discussion
Filter different columns to display in the table.
Hi Elemesey04
If you want the target type to appear beside Sales, use a table visual instead, since only scalar values can go into the Values area of a matrix. Adding a non-numeric field to the matrix requires it to be aggregated (for example, using Count, First, or Last).
As others have suggested, you can use field parameters to control which columns are visible. Field parameters can also be used to display both the target type and the accomplishment rate.
Please see the attached pbix.
Hello Elemesey04 ,
most probably this is possible, but I'm still not completely sure what you exactly need.
Perhaps a small demo will help us to find out:
There is one slicer to select the target type including the original target. Depending on this slicer the columns "Target Type", "Target" and "Accomplish Rate %" are calculated.
The definition of the rows of the matrix and their hierarchy depends on the "Group By" slicer. You can even change the hierarchy levels by changing the order of selection.
Is it that what you want?
Hi Elemesey04,
yes, you can😊 You find the link at the end of this posting.
Here are a few tipps on how I procesded:
- I started with your sample file
- Removed some empty rows from your two fact table using Power Query
- You should change back the path to the excel source file according to your needs
- Created a dimension table for Product Groups using DAX
- Created a date table using DAX
- Defined relationships between your two fact tables and the newly created tables
- Defined a measure table using "Enter data" (and removing the empty column after creating the first measure)
- Created a field parameter for Target Type including a slicer
- Created a field parameter for Group By including a slicer
- Changed the slicer settings a bit (e.g. only single selection for Target Type)
- Created the necessary measures
- Assigned "Group By" to the rows of the matrix visual
- Assigned the measures "Sum of Sales", "Target Type", "Sum of Target by Type" and Accomplish Rate %" to the Values
https://my.hidrive.com/lnk/F323wp2To
Hope that works for you. I you are fine with the solution I would be happy if you would accept it as such.
Super UserHi Elemesey04 , sounds like field parameters could be a solution:
- Create a field parameter: Modeling->New parameter->Fields
- Give the parameter a name
- Drag all the columns you want to "Add and reorder fields"
- Leave the "Add slicer to this page" checked
- Assign for example your region column to a new table visual
- Assign the only column from your newly created field parameter table to the table visual
- Play with the slicer to selected or deselect columns
Does that work for you? Do you need any further assistance?
Hello Hans-Georg_Puls , thank you for your support 😊 However, I need the monthly and product group reports summarized in a Table Matrix visual. Do you think this is possible?
