Forum Discussion
Create new table that sums rows based on matching column value
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") )
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 🙂
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 |
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/2449543Ah. 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.
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") )