Help with grouping and concatenating!

[Syndicate_Admin]

Please and thanks in advance to someone who can help me, I have the following situation with a database that I already grouped previously:

This is the situation I am counting on

Invoice Date	NIT Manager	Invoice No.	Cod_Anexo	Grouped Weight (Kg)	NIT AND
31/03/2023	3589	31	PLF	6.610,00	3589
31/03/2023	3589	31	PLR	2.811,00	3589
18/01/2023	3640	53	PLF	2.215,00	9190
27/05/2023	3589	53	PLR	4.183,00	3589
1/06/2023	3589	54	PLR	12.393,00	3589
11/11/2023	3345	54	PLR	2.372,00	4444

But the desired situation is to be able to group the "Invoice No." column even more and have the "Cod_Anexo" data concatenated, in addition to adding the corresponding "Grouped Weight" column data, something like this:

Invoice Date	NIT Manager	Invoice No.	Cod_Anexo	Grouped Weight (Kg)	NIT AND
31/03/2023	3589	31	PLF, PLR	9.421,00	3589
18/01/2023	3640	53	PLF, PLR	6.398,00	9190
1/06/2023	3589	54	PLR	12.393,00	3589
11/11/2023	3345	54	PLR	2.372,00	4444

[v-yifanw-msft · Accepted solution]

Hi @Syndicate_Admin ,
Depending on the information you provided, you can refer to the following steps:
1.Add three columns, filter results with Flag=0.

Cod_Anexo_1 = 
IF (
    CALCULATE (
        COUNT ( 'Table'[Cod_Anexo] ),
        FILTER (
            'Table',
            'Table'[Invoice No.] = EARLIER ( 'Table'[Invoice No.] )
                && 'Table'[Cod_Anexo] = EARLIER ( 'Table'[Cod_Anexo] )
        )
    ) = 2,
    'Table'[Cod_Anexo],
    'Table'[Cod_Anexo_]
)

Flag = 
VAR _1 =
    RANKX (
        FILTER ( 'Table', 'Table'[Invoice No.] = EARLIER ( 'Table'[Invoice No.] ) ),
        'Table'[Index],
        ,
        ASC
    )
VAR _2 =
    IF ( _1 = 1 || 'Table'[Cod_Anexo_1] <> "PLF,PLR", 0, 1 )
RETURN
    _2

WEIGHT = 
VAR _1 =
    CALCULATE (
        SUM ( 'Table'[Grouped Weight (Kg)] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Invoice No.] = EARLIER ( 'Table'[Invoice No.] )
        )
    )
RETURN
    IF ( 'Table'[Cod_Anexo_1] = "PLF,PLR", _1, 'Table'[Grouped Weight (Kg)] )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

In the result table, why do we not have 2 rows for invoice 53 when in fact Invoice Date and NIT Mangaer entries are differnet for both rows of that invoice number?

[Syndicate_Admin]

Hello, yes, I'm sorry, it was my mistake, they must be separated, the only one that would be grouped together would be invoice 31

[Ashish_Mathur]

Hi,

Should grouping of rows happen based on the first 3 columns?