Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Hi,
Try this
=SUM(MainDataTable[Revenue])
Hope this helps.
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?
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.
Thank You! This worked perfectly. Just for my understanding, can you explain why using EARLIER in the formula made it work?
You are welcome. In simple language, EARLIER() can be substituted with CURRENTROW().
Thank you! great to know.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |