cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MSquiers Frequent Visitor
Frequent Visitor

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:

YearJob NumberJob NameClientRevenueProfitTotal RequestsTotal Approved Requests
201010-01-04Client 1 SW 2010Client 1$155,00015,00054
201414-04-05Client 3 SW 2014Client 3$20,0002,50071
201818-03-05Client 2 SW 2012Client 2$35,000$5,00033

 

I am working on creating a table that generates sums for revenue, profit, total request, total approved:

YearRevenueProfitTotal RequestsTotal Approved Requests 
2010Sum of Revenue column with 2010 YearSum of Profit column with 2010 YearSum of Total Requests column with 2010 YearSum 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Table that sums based off Year data and is filterable by client

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?

Clients.jpg

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Re: Table that sums based off Year data and is filterable by client

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?

Clients.jpg

View solution in original post

MSquiers Frequent Visitor
Frequent Visitor

Re: Table that sums based off Year data and is filterable by client

That is exactly what I was looking for. Thank you so much. 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors