Group Total at Underlying Line Level

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

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]

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])

[Anonymous]

Hi Ashish, 

Thanks, but I get an error also, so that is not a viable option.

I was able to find a solution by adding this measure to the Accounts table instead of the Orders table

=CALCULATE(SUM('Order'[Amount]),ALLEXCEPT('Account','Account'[Company]))

For now this seems to be working for what I need (Ranking by Company amount including all orders), however I will need to watch to make sure that additional filters dont affect it.