Sort transaction file into Sales Summary

[Rogiv]

Hi everyone, I have a huge file about transaction that has 5 columns (Date, Product Name, Quantity, Price, Total Price), example like below:

Date Product Name Quantity Price Total
2019-01-01 A 2 $100 $200
2019-01-01 B 5 $50 $250
2019-01-01 B 50 $50 $2,500
2019-01-01 C 3 $30 $90
2019-02-01 B 1 $50 $50
2019-02-01 B 5 $50 $250
2019-02-01 B 1 $50 $50
2019-02-01 B 10 $50 $500
2019-03-01 C 3 $30 $90
2019-03-01 B 1 $50 $50

My question is, is there a way to sort this file into a total amount sold for every product in every month?

I expect the output will be something like:

Jan Feb Mar Total
A 2 0 0 2
B 55 17 1 73
C 3 0 3 6


Thank you in advance!

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Product	Quantity	Price	Total			Jan	Feb	Mar	Total
2	01/01/2019	A	2	$100	$200		A	2	0	0	2
3	01/01/2019	B	5	$50	$250		B	55	17	1	73
4	01/01/2019	B	50	$50	$2,500		C	3	0	3	6
5	01/01/2019	C	3	$30	$90
6	01/02/2019	B	1	$50	$50
7	01/02/2019	B	5	$50	$250
8	01/02/2019	B	1	$50	$50
9	01/02/2019	B	10	$50	$500
10	01/03/2019	C	3	$30	$90
11	01/03/2019	B	1	$50	$50
Sheet1

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(($B$2:$B$11=$G2)*(MONTH($A$2:$A$11)=MONTH("1/"&H$1)),$C$2:$C$11)

 

[sandy666]

with PowerQuery

Date	Product	Quantity	Price	Total		Product	January	February	March	Total
01/01/2019​	A	2​	$100	$200		A	2​	0​	0​	2​
01/01/2019​	B	5​	$50	$250		B	55​	17​	1​	73​
01/01/2019​	B	50​	$50	$2,500		C	3​	0​	3​	6​
01/01/2019​	C	3​	$30	$90
01/02/2019​	B	1​	$50	$50
01/02/2019​	B	5​	$50	$250
01/02/2019​	B	1​	$50	$50
01/02/2019​	B	10​	$50	$500
01/03/2019​	C	3​	$30	$90
01/03/2019​	B	1​	$50	$50

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RC = Table.RemoveColumns(Source,{"Price", "Total"}),
    Month = Table.TransformColumns(RC, {{"Date", each Date.MonthName(_), type text}}),
    Pivot = Table.Pivot(Month, List.Distinct(Month[Date]), "Date", "Quantity", List.Sum),
    TotalRows = Table.AddColumn(Pivot, "Total", each List.Sum({[January], [February], [March]}), type number)
in
    TotalRows[/SIZE]

 

[sandy666]

or simpler version

Date	Product	Quantity	Price	Total		Sum of Quantity	Date
Jan​	A	2​	$100	$200		Product	Jan​	Feb​	Mar​	Grand Total
Jan​	B	5​	$50	$250		A	2​			2​
Jan​	B	50​	$50	$2,500		B	55​	17​	1​	73​
Jan​	C	3​	$30	$90		C	3​		3​	6​
Feb​	B	1​	$50	$50		Grand Total	60​	17​	4​	81​
Feb​	B	5​	$50	$250
Feb​	B	1​	$50	$50
Feb​	B	10​	$50	$500
Mar​	C	3​	$30	$90
Mar​	B	1​	$50	$50

change Date to custom format: mmm then create PivotTable

 

[Rogiv]

I finally did a pivot table I think that is the most efficient way. Anyway, thank you!

 

[sandy666]

You are welcome

Have a nice day