Matrix with Slice Filters and Fixed Columns

[eudesmcf]

I have 2 tables, a fact and another dimension, I need create a slicer for filter the columns indicator (multi-select) and create fixed columns at don't I'll be filtered.

Fact

Code	Indicator	Value
1	2016	63,33
1	2017	60,84
1	2018	60,59
1	2019	57,93
1	2020	72,96
1	2021	65,64

Dim

Indicator

2016

2017

2018

2019

2020

2021

Matrix No Filtered

2016	2017	2018	2019	2020	2021	Average	Quantity	Sum
63,33	60,84	60,59	57,93	72,96	65,64	63,54833	6	381,29

Matrix Filtered (2016 and 2017)

2016	2017	Average	Quantity	Sum
63,33	60,84	62,085	2	124,17

[v-mengmli-msft · Accepted solution]

Hi @eudesmcf ,

I recommend you create a custom table with new rows: Average, Quantity. Then use If DAX to connect the new table and original table.

New custom table.

CustomTable  = ADDCOLUMNS( UNION( VALUES('OriginalTable'[Indicator]),{"Average"},{"Quantity"} ) , "index" , IF([Indicator]="Average"|| [Indicator]="Quantity" ,99,0))

Measure.

Measure  = IF(MAX(' CustomTable '[Indicator]) ="Average" ,DIVIDE(  SUM('OriginalTable '[Value])  ,  COUNTROWS(' OriginalTable ')),IF(MAX('CustomTable'[Indicator])="Quantity",COUNTROWS('OriginalTable') ,   
CALCULATE( SUM('Original'[Value]) , 'Original'[Indicator] in VALUES('CustomTable[Indicator])  ,VALUES('OriginalTable'[Indicator])  ) ))

Here is my test with my data for your reference.

My original table.

My custom table.

My measure.

When I select one of ITEM.

Best regards,

Mengmeng Li

[eudesmcf]

can you send me your pbix? when I filter the values another change in reaction. When I try set a matrix with a Code (column on Fact) they lost the context.

[Ritaf1983]

Hi @eudesmcf 
You can achieve it with the calculated groups, please refer to the linked video:
https://www.youtube.com/watch?v=Ps-yxG_fVQM

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly