Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 |
Solved! Go to 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
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.
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
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |