Filter by Column heading in matrix visual

[Anonymous]

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.

[MFelix · Accepted 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

[Shaina_Gupta]

Hi,

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

[Anonymous]

@Shaina_Gupta

Did you turn off single select on the slicer?

[Anonymous]

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.