Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Group Total at Underlying Line Level

Hi, 

 

I have two tables with the following columns:

Table1:Company, Account Number

Table2:Account Number, Order, Amount (Order Amount)

 

There can be multiple Account Numbers per Company

 

I am trying to set up a Table Visualization with the following columns:

Table1[Company], Table1[Account Number], Table2[Order], Table2[Amount], Table2[Company _Total]

 

the last column Table2[Company Amount] would be a measure which sums the Amount for all Orders across all Account Numbers for a given Company. I currently have the below formula which will return the value I am looking if and only if I have just the Company and Total Amount. As soon as I add any of the underlying data fields (Account Number, Order, Amount) then the Total measure updates to reflect the current lowest level of data.

 

Company_Total = SUMX(VALUES('Table1'[company]),CALCULATE(SUM('Table2'[Amount])))

 

I Tried creating a calculated table using SUMMARIZECOLUMNS and just having the Company and Total which again gave me the value i was looking for, however trying to add the total from that Calculated Table to a visual containing fields from other tables throws an error (out of memory).

 

Can anyone please suggest a formula i can use witch will maintain the highest level sum by company? I am also not married to using a measure if a Calculated Column would be better.

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Ashish, 

       

      While i cant post my actual file (confidentiality) i recreated the tables and data using dummy information and got the same issues. The file can be found here Google Drive Shared File

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        The measure should be this

         

        =CALCULATE([TOtal Amount],ALL(Orders[Order]),ALL(Accounts[AccountNumber]))

         

        When i try it, i get an out of memory error box.  I dragged Company from the Company_Total Table, Account Number from the Accounts Table.

         

        Total Amount = SUM(Orders[Amount])