Grouping and unique values

[UDMH]

Hi All,

This is my requirement, help me on this. Whether we can achieve this in Power BI.

This is my data for example,

Code	Name	Description	Text	Amount
1	AAA			120
1	AAA	Descrip 1	Text data 1	130
1	AAA	Descrip 1		100
2	BBB		Text data 2	50
2	BBB	Descrip 2	Text data 2	100
2	BBB	Descrip 2		100
2		Descrip 2		50

And, the output,

Code	Name	Description	Text	Amount
1	AAA	Descrip 1	Text data 1	350
2	BBB	Descrip 2	Text data 2	300

[Irwan · Accepted solution]

hello @UDMH 

please check if this accomodate your need.

create new table with following DAX.

Summarize =
SUMMARIZE(
    FILTER(
        'Table',
        'Table'[Description]<>BLANK()&&
        'Table'[Text]<>BLANK()
    ),
    'Table'[Code],
    'Table'[Name],
    'Table'[Description],
    'Table'[Text],
    "Amount",
    CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Code]))
)

Hope this will help.

Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to create a new table is writing DAX formula something like below.

Please check the below picture and the attached pbix file.

expected result table = 
	SUMMARIZECOLUMNS(
		Data[Code],
		Data[Name],
		Data[Description],
		Data[Text],
		FILTER(
			Data,
			Data[Name] <> BLANK() && Data[Description] <> BLANK() && Data[Text] <> BLANK()
		),
		"@Amount", CALCULATE(
			SUM(Data[Amount]),
			ALLEXCEPT(
				Data,
				Data[Code]
			)
		)
	)

[shafiz_p · Accepted solution]

Hi @UDMH ,
You could try the below simple version using Dax:

NewTable = 
SUMMARIZE(
    'Table',
    'Table'[Code],
    "Name", MAX('Table'[Name]),
    "Description", MAX('Table'[Description]),
    "Text", MAX('Table'[Text]),
    "Amount", SUM('Table'[Amount])
)

Also, you could use power query group by transformation. See images below:

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,

Shahariar Hafiz

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to create a new table is writing DAX formula something like below.

Please check the below picture and the attached pbix file.

expected result table = 
	SUMMARIZECOLUMNS(
		Data[Code],
		Data[Name],
		Data[Description],
		Data[Text],
		FILTER(
			Data,
			Data[Name] <> BLANK() && Data[Description] <> BLANK() && Data[Text] <> BLANK()
		),
		"@Amount", CALCULATE(
			SUM(Data[Amount]),
			ALLEXCEPT(
				Data,
				Data[Code]
			)
		)
	)

[divyed]

Hello @UDMH ,

Here is the dax based on your data.

// I have created a sample table named Sample_Table with 4 columns Code_Name, Description, Text and Amount.

//You can make use of Summerize function to achieve the same. 

SummarizedTable =

SUMMARIZE(

    Sample_Table,                         // Replace with your table name

    Sample_Table[Code_Name],                   // Group by Code

    "Description",

        MAXX(

            FILTER(Sample_Table, Sample_Table[Description] <> BLANK()), Sample_Table[Description]   // Get first non-blank Description

        ),

    "Text",

        MAXX(

            FILTER(Sample_Table, Sample_Table[Text] <> BLANK()), Sample_Table[Text]   // Get first non-blank Text

        ),

    "Total Amount",

        SUM(Sample_Table[Amount])          // Sum of Amount

)

I hope this will solve your problem.

Please mark this as solution if it has solved your problem. I appreciate your Kudos :).

Cheers

[Chetan007]

Hii @UDMH 

Please try this code

[Irwan · Accepted solution]

hello @UDMH 

please check if this accomodate your need.

create new table with following DAX.

Summarize =
SUMMARIZE(
    FILTER(
        'Table',
        'Table'[Description]<>BLANK()&&
        'Table'[Text]<>BLANK()
    ),
    'Table'[Code],
    'Table'[Name],
    'Table'[Description],
    'Table'[Text],
    "Amount",
    CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Code]))
)

Hope this will help.

Thank you.