topic Re: How to duplicate value in matrix visual in DAX Commands and Tips

How to duplicate value in matrix visual

nbufff — Wed, 18 Dec 2024 07:01:30 GMT

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!

Re: How to duplicate value in matrix visual

dharmendars007 — Wed, 18 Dec 2024 07:53:12 GMT

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

Re: How to duplicate value in matrix visual

v-hashadapu — Wed, 18 Dec 2024 12:19:15 GMT

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.

Re: How to duplicate value in matrix visual

nbufff — Thu, 19 Dec 2024 01:23:46 GMT

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?

Re: How to duplicate value in matrix visual

nbufff — Thu, 19 Dec 2024 01:45:34 GMT

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)

Re: How to duplicate value in matrix visual

v-hashadapu — Thu, 19 Dec 2024 08:37:17 GMT

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.

Re: How to duplicate value in matrix visual

nbufff — Thu, 19 Dec 2024 08:44:29 GMT

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.

Re: How to duplicate value in matrix visual

v-hashadapu — Fri, 20 Dec 2024 11:28:01 GMT

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.

Re: How to duplicate value in matrix visual

nbufff — Mon, 23 Dec 2024 00:15:26 GMT

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