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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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