Forum Discussion
ddalton
Resolver I
Resolver ICreate new table that sums rows based on matching column value
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.
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") )
9 Replies
ddaltonAs simple as that 😉
Thanks!
- SpartaBI
Community Champion
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 🙂