Add a measure in a pivot table, but just want to see the total

[Lolo]

Hello,

I have data and a pivot table on it, and I want to add a measure, but just want to see the total of this measure.

Pb is that the measure is applied on each column. This is a normal behavior of pivot table, but in my particular case, I just want to see the global total.

I'm wondering powerpivot could help to do that.

Please see the file attached, I have tried to explain my problem, easier to understand with a basic example.

Thank you in advance.

 

[Chihiro]

Can't you just group Units?

Edit: You may need to unpivot UNIT & UNIT2 columns in PowerQuery/Pivot.

 

[Lolo]

In this basic example, indeed if I group it would be ok.
But In my real data, there is several pivot rows (region/country/vehicule/, so grouping, will still keep all theses pivot rows.

The user want to see the detail, but quickly see the total of units 2 just at the right of the detailed pivot table, he doesn't want 1 grouped pivot table or 2 pivot tables.

but this gives me an idea
If indeed I create a second pivot table just after the first one, with jsut the measure UNIT 2, and I hide all the details except the total column, this should work. Problem is that when I filter the pivot table 1, I need to also filter accordingly the pivot table 2. With slicers it can be easy, but if the user filter directly the pivot table 1, I will probably need some macro.

 

[Chihiro]

You can set up hidden sheet with slicer to connect pivot tables and add pivot with filter only to control it. No need for VBA.

Method detailed in link.
http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/