locked
Display number value without aggregation RRS feed

  • Question

  • Hi,

    I need to display unit price and volume in my PoweverPivot table. The volume will be aggregated, but unit price should not.  Is there a way to do that?

    Fact table

    Souce Dest  UnitCost Volume
    S1 D1 0.5 100
    S2 D1 0.5 100

    Aggregated by Dest in Pivote Table

    Dest UnitCost Volume
    D1 0.5

    200

    I guess that I may need to create a measure for UnitCost, but not sure how as I have just statred using PowerPivote.  Any suggestions will be greatly appreciated.

    jwang

    Tuesday, April 9, 2013 2:07 PM

Answers

All replies

  • Can you simply replace SUM([UnitCost]) with AVERAGE([UnitCost]) or MAX([UnitCost])?  This should work if UnitCost is always the same for every Dest and if that is what you always use to aggregate.  Since you want the sum of volume you can leave that as SUM([Volume]).
    Tuesday, April 9, 2013 3:12 PM
    Answerer
  •  Thanks a lot for your feedback.

     Yes, I have tried with AVERAGE([UnitCost]) or MAX([UnitCost]).  It does shows what I need. Is there a way I can ask PowerPivot not to do the subtotal or grand total for that measure (Unit Cost)?

    Tuesday, April 9, 2013 4:49 PM
  • There are a couple different methods for supressing subtotals.

    A simple way would be to do something like this:

    =IF( HASONEVALUE( Tablename[Dest] ), AVERAGE( TableName[UnitCost] ), BLANK() )

    This works fine as long as the subtotals always have more than 1 Dest.

    See this post on PowerPivotPro:

    http://www.powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

    • Marked as answer by jwang_ga Thursday, April 11, 2013 8:08 PM
    Tuesday, April 9, 2013 4:54 PM
    Answerer
  • Thanks a lot. That does the trick.

    However, when I tried to add the similar measure on another table, I got the

        "A field with the name <measure name>  already exists in the PivotTable. Choose a different name."

    No matter what name  I used for the measure, it threw the error.  What is going on?

    Wednesday, April 10, 2013 2:32 PM
  • Thanks a lot. That does the trick.

    However, when I tried to add the similar measure on another table, I got the

        "A field with the name <measure name>  already exists in the PivotTable. Choose a different name."

    No matter what name  I used for the measure, it threw the error.  What is going on?

    Hi jwang_ga,

    It seems that you original issue has been solved. Right?

    For the new question, please refer to the following article:
    Renaming a Pivot Table Value Field: http://blog.contextures.com/archives/2009/05/04/renaming-a-pivot-table-value-field/

    If this issue still persisits, I suggest you open a new case and elaborate your issue with more detail for better support.

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, April 11, 2013 9:31 AM