Forum Discussion
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 |
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") )
In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.
SpartaBI Logo
Visit SpartaBI website Visit SpartaBI Linkdin Visit SpartaBI Facebook
Showcase Report – Contoso By SpartaBI
9 Replies
- SpartaBI
Community Champion
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") )
In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.
SpartaBI Logo
Visit SpartaBI website Visit SpartaBI Linkdin Visit SpartaBI Facebook
Showcase Report – Contoso By SpartaBI
ddaltonAs 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 🙂