cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Filter by Column heading in matrix visual

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:

columns.gif

 

Here is the pbix file.

 

Regards,

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

4 REPLIES 4
Highlighted
Super User V
Super User V

Re: Filter by Column heading in matrix visual

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:

columns.gif

 

Here is the pbix file.

 

Regards,

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Anonymous
Not applicable

Re: Filter by Column heading in matrix visual

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. 

 

Highlighted
Regular Visitor

Re: Filter by Column heading in matrix visual

Hi,

 

I tried using your approach but I am unable to view multiple selections in my matrix.

Highlighted
Anonymous
Not applicable

Re: Filter by Column heading in matrix visual

@Shaina_Gupta

 

Did you turn off single select on the slicer?

single select.PNG

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors