Chart showing growth over time
-
Wednesday, February 13, 2013 3:39 AMI am trying to create a chart in excel and using powerpivot. Version is 2010. I would like to identify the adoption of applications in my company over time and have the data I need just not cleanly.
For reference relevant fields in powerpivot I'm using are:
Table1: Applications
Columns: application name, country, users in country, date deployed, quarter, year, quarter-year
Table2: Countries
Columns: country, users in country
I understand table2 is duped in ways but may be useful. The data is like
Applications table:
App1, Russia, 50, 12/20/2012,Q4, 2012, Q42012
App2, Russia, 50, 01/15/2012,Q1,2012,Q12012
App3, Russia, 50, 03/13/2012,Q1,2012,Q12012
App1, Canada,40,01/13/2012,Q1,2012,Q12012
App2, China,300,01/16/2013,Q1, 2013, Q12013
Countries table:
Russia,50
Canada,40
China,300
So the applications table is repetitive of countries and user counts. It also has a list of dates the country first used the app. The county table just says for this country here is the number of users.
Application and countries table linked by country name
I want a report that filters in when a country first came on board using the applications and total count. Not country specific just totals per quarter. In the above case the chart would show graphically of course:
Q12012: 90
Q42012: 90
Q12013: 390
So I only care about the oldest date a country started using the applications and calculate total adoption over time. So as you see the first one listed is 90 just one Russia and one Canada. Then Q4 even though Russia adopted more they are already users so not new users. Then in Q1 2013 China came on board so total adoption users was now 300.
Any idea how to do this? Open to provide any clarification necessary.
Thanks.
- Edited by syarbrou1 Wednesday, February 13, 2013 3:40 AM
- Edited by syarbrou1 Wednesday, February 13, 2013 3:41 AM
- Edited by syarbrou1 Wednesday, February 13, 2013 3:43 AM
- Edited by syarbrou1 Wednesday, February 13, 2013 3:45 AM
- Edited by syarbrou1 Wednesday, February 13, 2013 3:45 AM
- Edited by syarbrou1 Wednesday, February 13, 2013 3:46 AM
- Edited by syarbrou1 Wednesday, February 13, 2013 3:46 AM
- Edited by syarbrou1 Wednesday, February 13, 2013 3:47 AM
All Replies
-
Wednesday, February 13, 2013 5:12 PM
Based on what I understood, this is what I did
1) Make a time dimension and leave it unrelated
2) Make a new measure
=sumx(values(Countries[Country]), if(calculate(min(App[date deployed])) <= max(DeployedDate[Date]), calculate(sum(Countries[Users])),0))
Ensure that the dates are all of date data type.
Cheers,
Jason | www.SqlJason.com
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
- Marked As Answer by syarbrou1 Sunday, February 17, 2013 1:19 AM
-
Saturday, February 16, 2013 5:35 PM
Thanks for the info. I think that's gonna do it. Just running into one item. If the application table deployed_date is blank, hence the quater of the year and year fields are blank, how do I exclude those? In my case I have 18 records without dates and it's showing on my chart as the first horizontal field with a blank quarter year (i.e. nothing on the bar just the number 18 with no value). Would like to have it only work with the values that have a date defined in the table. Thoughts?
Thanks.
-
Saturday, February 16, 2013 11:15 PM
I think I figured it out. Would this be accurate?
=sumx(values(Table_country[Country]), if((calculate(min(Table_country[Device Date])) <= max(DimDate[Date]) && (calculate(min(Table_Country[Device Date])) <> BLANK())), calculate(sum(Table_country[Device Count])),BLANK()))
Excuse if my table names are different. I've tweaked a few things since writing this inquiry. Plus DimDate is my Time Dimension.
Thanks.
-
Sunday, February 17, 2013 12:43 AM
Your formula will work
=sumx(values(Table_country[Country]), if((calculate(min(Table_country[Device Date])) <= max(DimDate[Date]) && (calculate(min(Table_Country[Device Date])) <> BLANK())), calculate(sum(Table_country[Device Count])),BLANK()))
The reason is because min() ignores NULL values, and hence if there is a country which has both blank values and dates, the min function considers only the dates (hence you will get the correct result). And in case there are no other dates apart from blank values, then min() will return blank and get filtered by the condition.
Cheers,
Jason | www.SqlJason.com
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
.png)
