Sum two rows of values under a column and create a row value for the sum

[Anonymous]

For the following .. I need to sum two row values and combine to one row value:

Sum "PwC Labs" + "Pwc Labs - Trust Tech" to one value called "PwC Labs Total", then remove the original two values

I created a measure but it adds a new column which I do not want .. any help would be greatly appreciated.

[speedramps]

I am a unpaid volunteer and it is getting late in the uk.

Please provide example input data in a table format ... not a screen print ... so anyone can import it and build a solution.

Also provide an example of the desired output and an explanation. like you have above.

If any other Superusers want to help PettyRoses before I get chance to reply, please do so. 😀

[Anonymous]

Hi .. sorry .. this definitely gets confusing .. but I don't need a new column .. I need a new value under column "Team" which sums two other values into one ... if I can do this in Excel with a pivot table . not sure why I can't do this in PBI

[Anonymous]

here is what I need:

BEFORE:

AFTER:

Does this make sense? Appreciate your help!

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

Column =
IF (
    'Table'[Team] IN { "PwC Labs", "PwC Labs - Trust Tech" },
    "PwC Labs Total",
    'Table'[Team]
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[speedramps]

Ahh .. sorry I misunderstood. You want an extra column not a extra row. My bad 🙄

In the query editor you can click on Transform and Unpivot Columns, which will create a row for each column.

Or you can create a list using Enter Data with

SortID,Team

1,Assurance

2,Consulting

3,PwC Labs

4,Pwc Labs - Trust Tech

In modelleing sort Team by SortID

Create measure for [Assurance], [Consulting], [PwC Labs] and [Trust Tech]

Then create a DAX measure

Answer =

SWITCH( SELEcTEDVALUE(listname[SortID]),

1, [Assurance]

2, [Consulting]

3, [PwC Labs]

4, [Pwc Labs] - [Trust Tech]
)

Then drag listname[Team]) and Team to a table visual.

It will create the desired output 😁😁😁

I helped you, now help me with kudos.

Please the click thumbs up and accept as solution buttons. Thanks 😎

[speedramps]

Hi PettyRoses

How much Power BI do you know ?

Do you understand the difference between:-

• Add columns. Which creates an extra column in the query editor.
• New column. Which creates an extra column in DAX
• New measure. Which creates formula in DAX but does not create a new column.

It is best pratice to use New measure.

You can then right click on the the other and click Hide.

You can use this syntax ...

PwC Labs Total =
SUMX(filename,  filename[PwC Labs] + filename[wc Labs] - filename[Trust Tech])

or like this 

PwC Labs Total =
SUM(filename[PwC Labs]) +
SUM(filename[wc Labs]) -
SUM(filename[Trust Tech])

I helped you, now please help me with kudos.

Click the thumbs up and accept as solution button.

Thanks ! 

[Anonymous]

Hi Speedramps,

Yes, I created a measure but it creates a new column in my table ... not the schema:

Measure Calc:

PwC_Labs_Team_Cnt = COUNTROWS(FILTER('Known Segment-US Tech Apps','Known Segment-US Tech Apps'[Team] IN {"PwC Labs","Pwc Labs - Trust Tech"}))

I need it to be a row value ...

Does this help clarify my issue?