Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Percent of Column

Unanswered Percent of Column

  • Thursday, April 12, 2012 3:45 PM
     
     
    Sign In to Vote
    0

    How do I add a generic percentage of column measure to my PowerPivot table?  The formula below works:

    Percentage of Column:=Count(Results[Respondent #]) / CALCULATE(COUNT(Results[Respondent #]), ALL(Results[MyColumn]))

    However I don't want to duplicate this forumula for all 40+ columns in my table...  Is there an easier way?

     

All Replies

  • Friday, April 13, 2012 3:24 AM
     
     
    Sign In to Vote
    0

    Hello,

    It would appear you could benefit from some 'data modeling', or re-organization of how the data is stuctured in your PowerPivot app.  For example, if you have a column for all Respondents instead of one column for each, you can then create a generic calculation that applies to all of them.

    If your source data is coming from, for example, a SQL based relational database, you will be able to issue targeted queries to 'shape' the data in a way that facilitates creation and maintenance of your PowerPivot app.



    Javier Guillen
    http://javierguillen.wordpress.com/

     
  • Friday, April 13, 2012 3:06 PM
     
     
    Sign In to Vote
    0

    Unfortunately the data comes from CSV files downloaded from a third party app.  What you get is

    Respondent #, Base, Position, Q1, Q2, Q3 ect..

    For PowerPivot to be a more effective solution then just doing Excel work it needs to be easier to get your data in and out then loading it into SQL or SSAS.  Fortunately, for the most part AllSelected() works for a lot of what I have needed.  The problem comes in when you want to do results by position and question or two questions.

     
  • Wednesday, May 02, 2012 10:41 AM
     
     
    Sign In to Vote
    0

    you can also ask your query on the below given forum. it may help you.

    http://www.donaldgooch.com/forums/showthread.php?t=33