PIVOT TABLE - get the distinct values not count

[EREX]

Hello
I need help with an issue i have and i can't figure how to do it with Pivot Table (or other way if easier).
I have a list of sales done for different products(product id, quantity, sold price, tota price, ) and i want to do a summery of all prudcts based on price sold, quantity at price sold and sum.
When i try to use Pivot i can't get this type of view because the same product could be sold and different prices. Pivto either gives me the avare of price sold or the sum but doesnt group the products by quantity sold at a price.

Here it is how my data looks like:

Product ID Quantity (units) Unit Price(euro) Total Sales (euro)
Product A 5 7 35
Product A 10 4 40
Product A 6 8 48
Product A 5 4 20
Product A 2 7 14
Product A 50 4 200
Product B 3 10 30
Product B 10 20 200
Product B 4 10 40
Product B 5 11 55
Product B 2 10 20
Product B 50 10 500


And i want to create the following type of summery table

Product ID Quantity (units) Unit Price(euro) Total Sales (euro)

Product A 7 7 49
Product A 65 4 260
Product A 6 8 48

Product B 59 10 590
Product B 10 20 200 .
Product B 5 11 55


Is there a way to do this with a pivot table or other easy way that doesnt take a lot of manual work since i have data list of 200k lines and many different price variations

Thank you in advance for your help.

Best Regards
R

[rorya]

It looks like you just need to put the unit price into the row field area instead of the values area.

[EREX]

YEs i Tried this but when i do this for some reason it does not group quantities it just shows them again.
I mean it groups some and some other not. i dont know why it does that . i have tried everything but same result.

[rorya]

You'll need to post a workbook then. There is no reason that would happen if the values are actually the same.

[EREX]

I have attached a sample of one of the cases.

As i said before for some cases id does the grouping by the price unit as supposed for some cases like the one in attached file it does not group it

Thank you again for the help

[rorya]

That is very strange. I can see the problem in your sample, but simply refreshing the pivot table makes it disappear for me!

[EREX]

Thas weird. I try refresh but does'nt work for me.*
I have checked the cell format and everything but still same issue. Cant figure out why it does that.

[rorya]

Have you tried changing the format on the source column then refresh the pivot table?

[EREX]

Actualy a just tried dobule click (to go in edit mode ) on data sheet on the cells which do not group together and than when i do refresh it recognizes them as same value.
I mean a created another column of unit price with the copy - paste values as values not formula then i double clicled the cell that do not group and made a refresh on the pivot and know it recognizes them as same values.

My problem is that i have thousand of rows and values and can not do this manually for all. Do you know somethin i can do that might solve this issue ?

Thank you

[CK76]

Your pivot table is based on data model.
See in PivotTable Analyze -> "Change Data Source" -> Connection Properties.

However, column data types (i.e. field properties) are not defined in the model.

To identify the underlying issue, select any cell in your pivot table. On PivotTable Analyze ribbon, go to "Calculations" section and click on OLAP tools -> Convert to formula.

Now check "P.Unit" field of converted formula.

For first P.Unit 4.9 It's defined as...

Please Login or Register  to view this content.

But for the second P.Unit 4.9 It's defined as...

Please Login or Register  to view this content.

So the item name is different between the two and not considered duplicate.

Best way to deal with this is... Instead of loading directly to Data Model from range, load to PowerQuery (Get & Transform) and define column data types.

For all columns that should be added to row label field, they should be defined as "Text". Any floating point error should be dealt in the query editor as well.

Then, when you are satisfied, load from query into data model.

EDIT: Alternately, if you don't need data model (i.e. you are not using DAX measures in your Pivot Table). You can simply recreate the pivot table without adding data to the model.
Make sure that you don't select "Add this data to the Data Model" option when you create the pivot table.
0.JPG