Forum Discussion

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

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 

ReferenceTypeValueCust
A123Type15000AAA
A123Type2150AAA
A123Type2300AAA
B321Type14500BBB
B321Type11500BBB
B321Type2100BBB
B321Type2250BBB
C231Type11000AAA
C231Type2350AAA
C231Type2150AAA

 

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

 

ReferenceType1Type2Cust
A1235000450AAA
B3216000350BBB
C2311000500AAA

 

  • ddalton's avatar
    ddalton
    Icon for Resolver I rankResolver 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
      Icon for Community Champion rankCommunity 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

      • ddalton's avatar
        ddalton
        Icon for Resolver I rankResolver I
        4 years ago

        Exactly like that. Thank you!