Trying to generate a distribution using a pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-1535456%22%20slang%3D%22en-US%22%3ETrying%20to%20generate%20a%20distribution%20using%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1535456%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20large%20dataset%20I%20am%20trying%20to%20analyse%20and%20I%20think%20I'm%20close%20but%20I%20can't%20quite%20figure%20out%20the%20last%20bit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20big%20data%20set%20has%20three%20columns%3A%20location%2C%20type%20and%20value.%20I%20want%20to%20be%20able%20to%20analyse%20the%20distribution%20of%20values%20for%20a%20given%20location%20and%20type.%20I'm%20trying%20to%20do%20it%20using%20a%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20build%20a%20pivot%20table%20that%20will%20filter%20for%20location%20and%20type.%20I%20can%20get%20it%20to%20give%20me%20each%20of%20the%20%3CEM%3Eunique%3C%2FEM%3E%20values%20in%20a%20column.%20But%2C%20all%20of%20the%20values%20that%20appear%20more%20than%20once%20disappear.%20Because%20what%20I%20am%20ultimately%20trying%20to%20do%20is%20to%20generate%20a%20distribution%2C%20that%20is%20no%20good%20to%20me%20-%20it%20matters%20how%20many%20entries%20there%20are%20for%20each%20unique%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20a%20pivot%20tale%20even%20the%20tool%20I%20need%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1535456%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536137%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20generate%20a%20distribution%20using%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F734858%22%20target%3D%22_blank%22%3E%40hunneric%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20something%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20465px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206875i604754D75C5AA32D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20build%20PivotTable%20in%20tabular%20form%20and%20add%20value%20both%20as%20field%20and%20aggregate%20as%20count%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20345px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206879i88F31BB92E17235B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a large dataset I am trying to analyse and I think I'm close but I can't quite figure out the last bit.

 

The big data set has three columns: location, type and value. I want to be able to analyse the distribution of values for a given location and type. I'm trying to do it using a pivot table.

 

I can build a pivot table that will filter for location and type. I can get it to give me each of the unique values in a column. But, all of the values that appear more than once disappear. Because what I am ultimately trying to do is to generate a distribution, that is no good to me - it matters how many entries there are for each unique value. 

 

Is a pivot tale even the tool I need?

 

Can anyone help? 

1 Reply
Highlighted

@hunneric 

If something like this

image.png

you may build PivotTable in tabular form and add value both as field and aggregate as count

image.png