Pivot table filtering on distinct values

Question

I am using a pivot table, and need to have it return the Distinct value for the Units.  So for example - for grouping #1 below - I need to get 500, and not the sum of that.  If I use the average, min, or max, I get the right value, but then the sub-totals and grand totals are based on the average/min/max of all the grouping, and not the sum of groups 1 to x.  I am not an expert, and tried putting into a data model, but then I could not add my own calculated fields

Grouping	Sum of Units	Sum of Total
group 1	6,000	$     98,000

This is a sample of ONE set of data.  There are columns not shown that get to the sum of the total that are not shown

Grouping	Units	Year 3 total
group 1	500	50000
group 1	500	60000
group 1	500	70000
group 1	500	10000
group 1	500	15000
group 1	500
group 1	500
group 1	500
group 1	500
group 1	500
group 1	500
group 1	500

Answer 1

Hi,

I have solved this with the PowerPivot.  You may download my solution workbook from here.

Hope this helps.

Answer 2

Hi,

try this , create a helper column named mysum and enter this formula into C2 then copy down =B2/COUNTIF($A$2:$A$13,A2)

now create pivot table >move Grouping field into Rows and my sum field into Values,

See snapshot for illustration

Hope it may helps you 

Regards

Answer 3

Hi pmw0813,

Have you referred to measure provided by Ashish? Please let us know, if you still need any help.

@Ashish, thanks for providing the solution.

Regards,

Chitrahaas

Answer 4

Hi,

This is the measure I have written

=if(HASONEVALUE(Data[Grouping]),MIN(Data[Units]),SUMX(SUMMARIZE(VALUES(Data[Grouping]),[Grouping],"ABCD",MIN(Data[Units])),[ABCD]))

Hope this helps.

Answer 5

We are not allowed to access onedrive externally -