Forum Discussion

eudesmcf's avatar
eudesmcf
Frequent Visitor
2 years ago
Solved

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 Indica...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
eudesmcf
Frequent Visitor
2 years ago

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.