Create new table that sums rows based on matching column value

[ddalton]

I have a table containing data with columns containing a reference (non-unique) and a type (non-unique). A representation is below:

TableA

Reference	Type	Value
A123	Type1	5521
A123	Type1	321
A123	Type2	10
B321	Type1	450
B321	Type2	300
C231	Type1	5000
C231	Type2	500
C231	Type2	550
...	...	...

I would like to create a new summary table that has a row for each reference and columsn that some the values for each type, as below:

TableB

Reference	Type1	Type2
A123	5842	10
B321	450	300
C231	5000	1050

I've created new Tables that simply Summarize columns from an existing Table and then created new Columns based on those (i.e., like the difference between two values etc.) but I'm not sure how to achieve this. 

[SpartaBI · Accepted solution]

@ddalton create this calculated table:

TableB = 
ADDCOLUMNS(
    VALUES('TableA'[Reference]),
    "Type1", CALCULATE(SUM('TableA'[Value]),'TableA'[Type] = "Type1"),
    "Type2", CALCULATE(SUM('TableA'[Value]),'TableA'[Type] = "Type2")
)

      

Showcase Report – Contoso By SpartaBI

[ddalton]

As simple as that 😉

Thanks! 

[SpartaBI]

@ddalton my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[ddalton]

The original table also has some other columns in it that would be useful. For example, there could be a customer reference that is specific to the reference 

Reference	Type	Value	Cust
A123	Type1	5000	AAA
A123	Type2	150	AAA
A123	Type2	300	AAA
B321	Type1	4500	BBB
B321	Type1	1500	BBB
B321	Type2	100	BBB
B321	Type2	250	BBB
C231	Type1	1000	AAA
C231	Type2	350	AAA
C231	Type2	150	AAA

So, in addition to your original solution, could I also include the Cust column in the data set? i.e.,:

Reference	Type1	Type2	Cust
A123	5000	450	AAA
B321	6000	350	BBB
C231	1000	500	AAA

[SpartaBI]

@ddalton for each refrence there can only be 1 customer?

Please don't check out my showcase report and give it a thumbs up over there if you liked it 🙂 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

[ddalton]

Ah. No, there is more than one reference per customer. I've updated the original reply above to include, for example, reference C231 and show that is also linked to Cust AAA.

So, there is multiple references per customer. 

[SpartaBI]

@ddalton you mean like this? :

TableB = 
ADDCOLUMNS(
    ALL('TableA'[Reference], TableA[Cust]),
    "Type1", CALCULATE(SUM('TableA'[Value]),'TableA'[Type] = "Type1"),
    "Type2", CALCULATE(SUM('TableA'[Value]),'TableA'[Type] = "Type2")
)

      

Showcase Report – Contoso By SpartaBI

[ddalton]

Exactly like that. Thank you! 

[SpartaBI]

@ddalton my pleasure 🙂
Don't forget about the report please 🤣. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂