none
Percent of Column

    問題

  • 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?

    2012年4月12日 下午 03:45

解答

所有回覆

  • 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/

    2012年4月13日 上午 03:24
    解答者
  • 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.

    2012年4月13日 下午 03:06
  • you can also ask your query on the below given forum. it may help you.

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

    2012年5月2日 上午 10:41
  • 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.


    SWAPA,

    Is this still an issue?

    Thank you!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    2013年10月28日 上午 07:09
    擁有者