How to create a calculated column that aggregates line by line using a filter

[Anonymous]

Hi, I would like to create a separate table based on an existing table that aggregates client revenue by year.  However, I can't seem to get a formula to aggregate the revenues by client.  

For example, 

The main Table contains the following information:

I created another table that summarizes all the client names from the Main Data Table

Table 2:

Client Name:

Client A

Client B

Client C

Then, I need to calculate the sum of the revenue for each client by year.   

I have tried the following formula: = Calculate(sum(MainDataTable[Revenue]),filter(MainDataTable, MainDataTable[Year]=2017)

The results ended up to be the total revenue for 2017 repeated for each client. 

Client Name     2017 Revenue

Client A                     135

Client B                     135

Client C                     135

I have also tried to add the client name to the filter: =Calculate(sum(MainDataTable[Revenue]),filter(MainDataTable, MainDataTable[Year]=2017 && MainDataTable[Client Name])

However I get an error message 

Any help would be much appreciated.

[Ashish_Mathur]

Hi,

Try this

1. Create a relatiosnhip from Table 2 to Table 1 on the Client Name column
2. In your Matrix visual, drag the Client Name column from Table 2 to the row labels and year to the column labels
3. Write this measure

=SUM(MainDataTable[Revenue])

Hope this helps.

[Anonymous]

Thank You for the response. However, I visual isn't the solution I'm looking for.  I need to create the separate table 2 because I will be building onto it.  So usuing a calculated column is the best solution for me at the moment. Any other possible suggestions?

[Ashish_Mathur · Accepted solution]

Hi,

Try these calculated column formulas in the Client sheet

=CALCULATE(SUM(MainDataTable[Revenue]),FILTER(MainDataTable,MainDataTable[Client Name]=EARLIER([Client Name])&&MainDataTable[Year]=2017))

=CALCULATE(SUM(MainDataTable[Revenue]),FILTER(MainDataTable,MainDataTable[Client Name]=EARLIER([Client Name])&&MainDataTable[Year]=2018))

Hope this helps.

[Anonymous]

Thank You! This worked perfectly.  Just for my understanding, can you explain why using EARLIER in the formula made it work?

[Ashish_Mathur]

You are welcome.  In simple language, EARLIER() can be substituted with CURRENTROW().

[Anonymous]

Thank you! great to know.