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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
101 | |
94 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
95 |