Forum Discussion

ddalton's avatar
ddalton
Resolver I
4 years ago
Solved

Create 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 ...
Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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's avatar
ddalton
Resolver I
4 years ago

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's avatar
SpartaBI
Community Champion
4 years ago

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