Forum Discussion
How to duplicate value in matrix visual
Dear Community, I was asked to duplicate some value from one group to other group, it is easy in excel but seemed stuck if that could be used by certain Measure in PBI.
My Case: I used below data to generate the matrix table.
| MAV Date | Line_Name | VS | Qty |
| 12/19/2024 | GroupA | Team9 | 5 |
| 12/18/2024 | GroupA | Team9 | 4 |
| 12/19/2024 | GroupB | Team9 | 3 |
| 12/18/2024 | GroupB | Team9 | 2 |
| 12/19/2024 | AC01 | Team 5 | 1 |
I need to put the Group B data under Team 9 to Team 5 as yellow high-lighted below.
My solution is to filter Group B data from above table and rename VS toTeam 5, save as new table, then use union to join this new table with original table. It works but is there any easy way e.g. set up certain measure to fulfill that?
Thanks for help!
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.
8 Replies
Hello nbufff ,
You can use a measure to sum quantities as if Group B under Team 9 appears under Team 5.Sum of Qty Adjusted =
VAR GroupBQty =
CALCULATE (
SUM ( 'OriginalTable'[Qty] ),
'OriginalTable'[Line_Name] = "GroupB",
'OriginalTable'[Line_NameVS] = "Team9")
RETURN
CALCULATE (
SUM ( 'OriginalTable'[Qty] )) +
IF (
SELECTEDVALUE ( 'OriginalTable'[Line_NameVS] ) = "Team5" &&
SELECTEDVALUE ( 'OriginalTable'[Line_Name] ) = "GroupB",
GroupBQty,0)If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!
Thank You
Dharmendar S
dharmendars007 Thanks for your advise. I applied your code below it seemed still same as before, could you pls check if I missed something?
Sum of Qty Adjusted = VAR GroupBQty = CALCULATE ( SUM ( 'Data'[Qty] ), 'Data'[Line_Name] = "GroupB", 'Data'[VS] = "Team9") RETURN CALCULATE ( SUM ( 'Data'[Qty] )) + IF ( SELECTEDVALUE ( 'Data'[VS] ) = "Team5" && SELECTEDVALUE ( 'Data'[Line_Name] ) = "GroupB", GroupBQty,0)
Hi nbufff ,Thank you for reaching out to Microsoft Fabric Community Forum.
Try this:
Create a Calculated Column:
Adjusted VS =
IF (
'Table'[Line_Name] = "GroupB" && 'Table'[VS] = "Team9",
"Team 5",
'Table'[VS]
)
Create a Measure for Total Quantity:
Total Qty = SUM('Table'[Qty])
Build Your Matrix Table:
Go to the Report view by clicking the Report icon on the left pane.
Insert a Matrix Visual from the Visualizations pane.
In the Fields pane, drag Adjusted VS to the Rows section.
Drag MAV Date (or any other appropriate column) to the Columns section.
Drag the Total Qty measure to the Values section.
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.
