locked
Chart showing growth over time RRS feed

  • Question

  • I 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:47 AM
    Wednesday, February 13, 2013 3:39 AM

Answers

  • 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! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by syarbrou1 Sunday, February 17, 2013 1:19 AM
    Wednesday, February 13, 2013 5:12 PM
    Answerer

All replies

  • 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! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by syarbrou1 Sunday, February 17, 2013 1:19 AM
    Wednesday, February 13, 2013 5:12 PM
    Answerer
  • 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 5:35 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.


    • Edited by syarbrou1 Saturday, February 16, 2013 11:18 PM
    Saturday, February 16, 2013 11:15 PM
  • 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! :)

    Some Random Thoughts

    Follow me on Twitter

    Sunday, February 17, 2013 12:43 AM
    Answerer