Forum Discussion
Matrix with Slice Filters and Fixed Columns
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 |
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
3 Replies
Hi eudesmcf
You can achieve it with the calculated groups, please refer to the linked video:
https://www.youtube.com/watch?v=Ps-yxG_fVQMIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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
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.
