How to duplicate value in matrix visual

[nbufff]

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!

[v-hashadapu]

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.

[nbufff]

@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?

[v-hashadapu · Accepted solution]

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]

)

2. 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.

[nbufff]

Thanks for your update. If there's no other way around, I accepted that as the solution.

[nbufff]

@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.

[v-hashadapu]

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.

[dharmendars007]

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

LinkedIN 

[nbufff]

@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)