How to add two rows?

[Tg20188]

Hello All!!

I'm quite new to PQ. I working with not very large dataset and I'm facing a bit challenge.

I have one query which looks like this:

Column 1   Column 2

40001           Amount 1

40002           Amount 2

40003           Amount 3

I have another query where i have a mapping table for Column 1.

Let's say it looks like this:

Column 1       Column 2

40001              Value 1

40002              Value 2

40003              Value 3

In my pivot table i need t retain Value 1, Value 2 and Value 3 but I also need "something" (I guess not a measure) which would allow me to add Value 2 and Value 3.

How would I solve it? 

Appreciate help!!

[Tg20188]

Hello everyone,

apologize I didn't provide responses.

I wanted to attach an example.

- "Financial data" tab is my dataset

- "Mapping" is basically mapping of Account #s in "Financial data"

- Each account has one of two values assigned: "Gross" or "Ceded"

When building Pivot table i want to have Gross and Ceded values in rows and Accounts in columns. This works fine. What I'm struggling with is how to add "Gross" and "Ceded" aggregations to calculate "Net" (which is sum of "Gross" and "Ceded").

I also added to the attached file "Output" tab which is just static presentation what i want to achieve.

let me know if this support helps. and thank you in advance for help 🙂

https://docs.google.com/spreadsheets/d/1AdEmeik50VrC-5ZQOz2EbJc00lCpXXu0/edit?usp=sharing&ouid=11115... 

[KNP]

Still need more detail.

From the mapping table, an account appears to be one or the other, never both Gross and Ceded.

So from what I can see in your sample data this would be your output...

	Account 1	Account 2	Account 3	Account 4	Account 5	Account 6
Gross	100	100	-20
Ceded				30	150	200
Net	100	100	-20	-30	-150	-200

What am I missing?

[v-yingjl]

Hi @Tg20188 ,

In my pivot table i need t retain Value 1, Value 2 and Value 3 but I also need "something" (I guess not a measure) which would allow me to add Value 2 and Value 3.

Could you please consider providing more detail about it? For example, which is your pivot table, the result table? or the source table; how to understand 'add Value 2 and Value 3' since the column is not number type etc.

In addition, if you can provide expected output, it would help better. Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Community Support Team _ Yingjie Li

[KNP]

Hi @Tg20188 - can you please place some actual sample data and desired output to give a better understanding of what you're trying to achieve?

[Tg20188]

One clarifcation Value 1, Value 2 and Value 3 are not numbers these are basically labels. Sorry for confusion!