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

Create new column from result of intermediate calcluation

Hi,

 

I've come across an interesting dilemma: I'm trying to calculate an attrition measure based on a yearly cohort. For example, I'd like to see: of customers that had revenue in 2015, how much revenue did those same customers have in 2016.

 

I have two tables: "Accounts" and "Projects".

 

Each project has an associated account. So, a certain account could have 3 or 4 projects in a year. However, revenue is only stored by project in the "projects" table. See below:

 

ProjectAccountRevenueDate
Project AClient A           5,0005/1/2015
Project BClient A         10,00010/1/2015
Project CClient A           7,00012/31/2015
Project DClient B           8,0007/1/2015
Project EClient B         10,0007/1/2016
Project FClient C           4,0003/1/2016
Project GClient D           6,0003/31/2015
Project HClient D           5,0009/30/2015
Project IClient D           2,0003/31/2016

 

I need to first get to revenue by year by account, which I can do by using the "matrix" visualization:

 

Account2015 Revenue2016 Revenue
Client A                 126,7720
Client B                   15,000                  10,000
Client C0                    4,000
Client D                   11,000                    2,000

 

However, from those results, I then need to perform calculations. I need to be able to calculate how much revenue did a client have in 2016 if, and only if, they had revenue in 2015. See below:

 

Account2016 revenue (2015 cohort)
Client A0
Client B                                           10,000
Client C0
Client D                                             2,000

 

My question is, is there a way to do this through DAX formulas? I'd like to create a new column in my "accounts" table that will sum the revenue by year by account from the "projects" table (again, revenue is not stored in the accounts table. It is only stored in the projects table).

 

I know that a SUMX function will allow me to get a total row sum based on filtering criteria from a different table, but I need to basically have it do this for each individual row rather than the entire column in aggregate.

 

Is this possible?

3 REPLIES 3
Super User
Super User

Re: Create new column from result of intermediate calcluation

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
DBECONP Frequent Visitor
Frequent Visitor

Re: Create new column from result of intermediate calcluation

@Ashish_Mathur Thanks! Would there be a way to do this if there were more than 2 years? For example, my data runs 2015 - 2019. I would like to see: of accounts that existed in 2015, which accounts had revenue in 2016, and 2017, 2018, 2019. 

 

I also need to eventually get to a view of a 2016, 2017, and 2018 cohorts. So, of accounts that had revenue in 2016, which had revenue in 2017, 2018, and 2019, and so on for each cohort. Is this possible in power bi?

Super User
Super User

Re: Create new column from result of intermediate calcluation

Hi,

Share some data and show the expected result with an explanation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 144 members 1,854 guests
Please welcome our newest community members: