Forum Discussion
How to duplicate value in matrix visual
Hi ,Thank you for reaching out to Microsoft Fabric Community Forum.
We can do that by creating a new table that duplicates the rows for GroupB under both Team9 and Team 5 and then combining this table with the original one using DAX.
1. Create a Duplicate Table:DuplicatedTable =
SELECTCOLUMNS (
FILTER (
'Table',
'Table'[Line_Name] = "GroupB" && 'Table'[VS] = "Team9"
),
"MAV Date", 'Table'[MAV Date],
"Line_Name", 'Table'[Line_Name],
"VS", "Team 5",
"Qty", 'Table'[Qty]
)
- Create a Combined Table:
CombinedTable =
UNION (
SELECTCOLUMNS (
'Table',
"MAV Date", 'Table'[MAV Date],
"Line_Name", 'Table'[Line_Name],
"VS", 'Table'[VS],
"Qty", 'Table'[Qty]
),
DuplicatedTable
)
3. Create a Measure for Total Quantity:
Total Qty = SUM('CombinedTable'[Qty])
4. Build a Matrix Visual
5. output will look like this:
If this helps, please mark it ‘Accept as Solution’, so others with similar queries may find it more easily. If not, please share the details.
v-hashadapu ,thanks for your advise. If I create the calculated column, the qty on group B will be moved to Team 5 but it will not be available under Team 9. Can we make it showing both on Team 5 and Team 9?
Hi ,Thank you for reaching out to Microsoft Fabric Community Forum.
We can do that by creating a new table that duplicates the rows for GroupB under both Team9 and Team 5 and then combining this table with the original one using DAX.
1. Create a Duplicate Table:
DuplicatedTable =
SELECTCOLUMNS (
FILTER (
'Table',
'Table'[Line_Name] = "GroupB" && 'Table'[VS] = "Team9"
),
"MAV Date", 'Table'[MAV Date],
"Line_Name", 'Table'[Line_Name],
"VS", "Team 5",
"Qty", 'Table'[Qty]
)
- Create a Combined Table:
CombinedTable =
UNION (
SELECTCOLUMNS (
'Table',
"MAV Date", 'Table'[MAV Date],
"Line_Name", 'Table'[Line_Name],
"VS", 'Table'[VS],
"Qty", 'Table'[Qty]
),
DuplicatedTable
)
3. Create a Measure for Total Quantity:
Total Qty = SUM('CombinedTable'[Qty])
4. Build a Matrix Visual
5. output will look like this:
If this helps, please mark it ‘Accept as Solution’, so others with similar queries may find it more easily. If not, please share the details.
v-hashadapu ,thanks for advice. That's also my way in dealing with this. I posted question here also want to learn if there's any other better solution without creating additional two tables.
Hi nbufff ,Thank you for reaching out to Microsoft Fabric Community Forum.
Unfortunately, there is no other workaround without creating at least one additional table. Please try this
Create a New Calculated Table:
DuplicatedTable =
UNION (
SELECTCOLUMNS(
'SalesData',
"MAV Date", 'SalesData'[MAV Date],
"Line_Name", 'SalesData'[Line_Name],
"VS", 'SalesData'[VS],
"Qty", 'SalesData'[Qty]
),
SELECTCOLUMNS(
FILTER('SalesData', 'SalesData'[Line_Name] = "GroupB" && 'SalesData'[VS] = "Team9"),
"MAV Date", 'SalesData'[MAV Date],
"Line_Name", 'SalesData'[Line_Name],
"VS", "Team 5",
"Qty", 'SalesData'[Qty]
)
)
Create a measure:
Total Qty = SUM('DuplicatedTable'[Qty])
Create a Matrix Visual:
Output from Matrix:
If this helps, please mark it ‘Accept as Solution’, so others with similar queries may find it more easily.
Thanks for your update. If there's no other way around, I accepted that as the solution.