Forum Discussion
Table that sums based off Year data and is filterable by client
I am currently working on reviewing some financial data for my company and I have not been able to figure out how to configure a specific table to calculate sums of data from another table based on year the work occurred and filterable by client.
The base data is a few hundred lines with data from 2010 to 2018 and is in the following excel format:
| Year | Job Number | Job Name | Client | Revenue | Profit | Total Requests | Total Approved Requests |
| 2010 | 10-01-04 | Client 1 SW 2010 | Client 1 | $155,000 | 15,000 | 5 | 4 |
| 2014 | 14-04-05 | Client 3 SW 2014 | Client 3 | $20,000 | 2,500 | 7 | 1 |
| 2018 | 18-03-05 | Client 2 SW 2012 | Client 2 | $35,000 | $5,000 | 3 | 3 |
I am working on creating a table that generates sums for revenue, profit, total request, total approved:
| Year | Revenue | Profit | Total Requests | Total Approved Requests | |
| 2010 | Sum of Revenue column with 2010 Year | Sum of Profit column with 2010 Year | Sum of Total Requests column with 2010 Year | Sum of Total Approved column with 2010 Year | |
| 2011 | |||||
| 2012 |
If I could then add a filter that would calculate the table for each year based off a specific client would be wonderful. I can figure out the table in Excel using sumif commands, but I need to make it robust enough that we can update the data each year when we close out the year. Any help on getting just the year and Revenue column data to work with the client filter would probably help me finish out the remainder of the table.
I have been trying to configure this for the past two days. Good with Excel and Visual Basic, but DAX has been a bit difficult to wrap my head around.
Hello MSquiers
Not sure I am understanding but I put together a .pbix file with sample data and some measures. Is this along the lines of what you were trying to do?
2 Replies
- jdbuchanan71
Super User
Hello MSquiers
Not sure I am understanding but I put together a .pbix file with sample data and some measures. Is this along the lines of what you were trying to do?
That is exactly what I was looking for. Thank you so much.
