Forum Discussion
Filter by Column heading in matrix visual
I have a matrix visual with 2 types of value. I would like to make a filter which allows the user to choose either one or both.
The matrix now looks as follow:
Year | 2016 2017
product | EUR L EUR L
-------------------------------------------------------------
Milk | 15289 374 13827 340
Soda | 40213 896 49990 916
etc....
The underlying information is as follow:
Comany product purchase date EUR L
Comany A Milk 12-05-2016 200 100
Comany A Soda 16-06-2016 160 85
Comany B Soda 10-10-2017 1000 550
etc...
The data set is quite large, so making a seperate column and unpivot is a bit too much work.
Is their maybe a simple way to make a slicer, which allows me to chooce to see in the matrix either EUR, L or both
As a bonus question. I there a way to add a column in the matrix that shows the difference between the shown value. Take into consideration the data goes back to 2011 and I alreadt have a filter that allows me to show the specific years or months I want to compare.
Thank you very much in advance, please let me know if you need more information to help me.
Hi Anonymous,
Theres is a similar request on this post and if you go to the exceleratorbi link on that message you have the explanation on how to achieve a measure in rows, however you can adjust this to your model to achieve it in columns.
I pick up your data and using the solution above work out this final result, see below the step by step and also a download for a pbix sample:
1 - Create a new table:
Measures Selection
ID | Measure
1 | EUR
2 | L
3 | Variation
2 - Create a measure on the Measures Selection table:
Selected_Measure = MAX('Measure_selection'[ID])3 - Create 4 measures on the Fact_Table (name given to the basic data table):
EUR Total = SUM(Fact_Table[EUR]) L Total = SUM(Fact_Table[L])
Variation = [EUR Total] - [L Total] Matrix_values = SWITCH ( [Selected_Measure]; 1 ; [EUR Total] ; 2 ; [L Total] ;
3 ; [Variation] )4 - Add the values to your visual matrix as explained:
Rows: Product (column Fact_Table)
Columns: Purchase Date - Year (column Fact_Table) + Measure (column Measure_selection)
Values: Matrix_values (measure from Fact_table
5 - Add a slicer with the Measure Column on your report.
Final result:
Here is the pbix file.
Regards,
MFelix
4 Replies
Hi Anonymous,
Theres is a similar request on this post and if you go to the exceleratorbi link on that message you have the explanation on how to achieve a measure in rows, however you can adjust this to your model to achieve it in columns.
I pick up your data and using the solution above work out this final result, see below the step by step and also a download for a pbix sample:
1 - Create a new table:
Measures Selection
ID | Measure
1 | EUR
2 | L
3 | Variation
2 - Create a measure on the Measures Selection table:
Selected_Measure = MAX('Measure_selection'[ID])3 - Create 4 measures on the Fact_Table (name given to the basic data table):
EUR Total = SUM(Fact_Table[EUR]) L Total = SUM(Fact_Table[L])
Variation = [EUR Total] - [L Total] Matrix_values = SWITCH ( [Selected_Measure]; 1 ; [EUR Total] ; 2 ; [L Total] ;
3 ; [Variation] )4 - Add the values to your visual matrix as explained:
Rows: Product (column Fact_Table)
Columns: Purchase Date - Year (column Fact_Table) + Measure (column Measure_selection)
Values: Matrix_values (measure from Fact_table
5 - Add a slicer with the Measure Column on your report.
Final result:
Here is the pbix file.
Regards,
MFelix
Thank you MFelix for your clear explenation , it works perfectly.
I always try to search on the forum first, but didn't find that post.
Hi,
I tried using your approach but I am unable to view multiple selections in my matrix.
