Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
I tried using your approach but I am unable to view multiple selections in my matrix.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |