# Display number value without aggregation

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

• 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 Thursday, April 11, 2013 8:08 PM
Tuesday, April 9, 2013 4:54 PM

### 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
•  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 Thursday, April 11, 2013 8:08 PM
Tuesday, April 9, 2013 4:54 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?

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