Combine multiple row values in one column to summarize as a new row value

[Anonymous]

I have a column that has several values, some of which need to be summarized as a separate row value:

Column Name: Team

Row Values:

TSIT

Trust Operation

Consulting

Tax

I need to combine row values "TSIT" and "Trust Operation" into one summary value (TSIT Total).  I am counting by asset type.

Column Name: Segment

Maintenance

I also need to add data from another column into the same row value to report as well.

My report should allow me to use the same column header "Team" with the asset type counted with the following rows summarized:

Column Name: Team

Row values with counts by asset:

TSIT Total

Consulting

Tax

Maintenance

I have a pivot table in Excel .. but cannot get this to work in Power BI.

Any help is greatly appreciated.

Thank you.

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

The best way to do this is probably to create a table like the one below, which hopefully meets your needs.

Table 2 =
DATATABLE (
    "Team", STRING,
    "Segment", STRING,
    "Asset_Nm", STRING,
    {
        { "LSIT Total", "2", "Maintenance" },
        { "Accounting", "1", "Maintenance" },
        { "Tax", "1", "Maintenance" }
    }
)

If the problem is still not resolved, please provide detailed error information and desired results screenshot. Looking forward to your reply.

Best Regards,
Henry

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

[amitchandak]

@Anonymous ,
Can you share sample data and sample output in table format?

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Anonymous]

Hi Amitchandak,

Thanks for responding .. here is some sample data ..

Team	Segment	Asset_Nm	Asset_Cnt
TSIT	Maintenance	Printer	1
Trust Operation		Laptop	1
Consulting		Desktop	1
Tax		Printer	1

First Part:

I need to combine rows "TSIT" and "Trust Operation" to count assets as '2' snice they are related but separate rows

Maintain the other row values

Report on:

Column Name: Team

TSIT Total (combined "TSIT" and "Trust Operation")

Consulting

Tax

Second Part:

Add the asset count for column "Segment", asset name "Maintenance" in same report column "Team"