Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
eudesmcf
Frequent Visitor

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

CodeIndicatorValue
1201663,33
1201760,84
1201860,59
1201957,93
1202072,96
1202165,64

 

Dim

 

Indicator
2016
2017
2018
2019
2020
2021

 

Matrix No Filtered

201620172018201920202021AverageQuantitySum
63,3360,8460,5957,9372,9665,6463,548336381,29

 

Matrix Filtered (2016 and 2017)

 

20162017AverageQuantitySum
63,3360,8462,0852124,17
1 ACCEPTED SOLUTION
v-mengmli-msft
Community Support
Community Support

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.

vmengmlimsft_0-1723625701554.png

 

My custom table.

vmengmlimsft_1-1723625724106.png

 

My measure.

vmengmlimsft_2-1723625748816.png

 

When I select one of ITEM.

vmengmlimsft_3-1723625778078.png

 

 

 

Best regards,

Mengmeng Li

View solution in original post

3 REPLIES 3
v-mengmli-msft
Community Support
Community Support

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.

vmengmlimsft_0-1723625701554.png

 

My custom table.

vmengmlimsft_1-1723625724106.png

 

My measure.

vmengmlimsft_2-1723625748816.png

 

When I select one of ITEM.

vmengmlimsft_3-1723625778078.png

 

 

 

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.

Ritaf1983
Super User
Super User

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.