locked
Calculated Field is Disable while using PowerPivot to access multiple sheet column in a single pivot, RRS feed

  • Question

  • Hi,

    I am trying to add a calculated field in my power pivot table to show the percentage on the basis of 2 columns, please let me know why this is not appearing and how can i fix it.

    Any other way if i can add a calculated field in the pivot table will be very helpful.

    Thanks

    Aman

     

    Tuesday, March 24, 2015 11:27 AM

Answers

  • Hi Aman,

    Could you kindly provide more details about your model i.e. the table structures and their relationships. This will help members of the forum to better understand the problem area.

    Based on what you've mentioned so far, you could try something along these lines:

    Create a calculated field that counts occurrence rows:

    Occurrences:=COUNTROWS(OccurrenceTable)

    Create a calculated field that counts the occurrence rows with a Total Expected Occurrence >= 4:

    Occurrences >= 4:=
    CALCULATE(
      COUNTROWS(OccurrenceTable), 
      OccurrenceTable[Total Expected Occurence] >= 4
    )

    Divide the [Occurrences >= 4] by [Occurrences]:

    % of Occurrences >= 4:= DIVIDE([Occurrences >= 4], [Occurrences])

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, April 11, 2015 10:17 AM

All replies

  • have you add the table to data model?

    KR

    Wednesday, March 25, 2015 2:14 AM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the Power Pivot forum

    https://social.technet.microsoft.com/Forums/office/en-US/home?forum=sqlkjpowerpivotforexcel

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Wednesday, March 25, 2015 2:15 AM
  • Thanks KR for the response,

    Yes I loaded multiple tables in the data model and using PoewrPivot ,

    i have a rating column, in that i have 0,1,2,3,4,5 rating numbers and we call it "Total expected occurrence", a single person can get same rating multiple times, if a person is getting >=4 rating no. we call it "Occurrence >= 4". now i have to find out the total count of "Occurrence >=4" for a particular person from "Total expected occurrence" column,

    once will get the  net "Occurrence >=4" for everyone, it should divide  "Occurrence >=4"/ "Total expected occurrence" to take out the percentage of rating 4 for a particular person  

    Best Wishes,

    Aman verma

    Thursday, March 26, 2015 12:54 PM
  • Aman,

    Is this still an issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

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

    Thursday, April 2, 2015 5:50 PM
  • Hi Aman,

    Could you kindly provide more details about your model i.e. the table structures and their relationships. This will help members of the forum to better understand the problem area.

    Based on what you've mentioned so far, you could try something along these lines:

    Create a calculated field that counts occurrence rows:

    Occurrences:=COUNTROWS(OccurrenceTable)

    Create a calculated field that counts the occurrence rows with a Total Expected Occurrence >= 4:

    Occurrences >= 4:=
    CALCULATE(
      COUNTROWS(OccurrenceTable), 
      OccurrenceTable[Total Expected Occurence] >= 4
    )

    Divide the [Occurrences >= 4] by [Occurrences]:

    % of Occurrences >= 4:= DIVIDE([Occurrences >= 4], [Occurrences])

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, April 11, 2015 10:17 AM