Forum Discussion
AdministratorHelp with grouping and concatenating!
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 |
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 _2WEIGHT = 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.
4 Replies
- Ashish_Mathur
Super User
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_AdminHello, 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?
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 _2WEIGHT = 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.
