Sales Report Power Query

[dbethig]

I am trying to make a sales dashboard/report. I am pulling data from workbooks within a specific folder. I have combined them into one table in Power Query but I am stuck on how to best display month & year comparisons for the data.

I can do it on a table with an Index Match formula but it's not part of the data set or a pivot table. If I add a column to the pivot table showing the difference to the prev month/year it only works when they are not filtered out.

The dashboard/report needs to only show the selected month (via a slicer currently) as we have a lot of artists to list.

Example data:

Artist	Year	Month	Sales
John Smith	2019	Sep	£34,656.41​
John Smith	2019	Oct	£1,329.16​
John Smith	2019	Nov	£36,623.60​
John Smith	2020	Sep	£25,300.52​
John Smith	2020	Oct	£51,799.10​
John Smith	2020	Nov	£4,070.83​
Anne	2020	Sep	£11,710.85​
Anne	2020	Oct	£9,409.09​
Anne	2020	Nov	£6,333.35​
Jane Doe	2019	Sep	£43,231.26​
Jane Doe	2019	Oct	£58,377.55​
Jane Doe	2019	Nov	£175,488.00​
Jane Doe	2020	Sep	£72,141.98​
Jane Doe	2020	Oct	£113,679.99​
Jane Doe	2020	Nov	£182,891.55​

 

[alansidman]

If this works for you, then

Book9
	F	G	H	I	J	K
1	Artist	Year	Month	Sales	Table2.Year	Table2.Sales
2	John Smith	2019	Sep	£34,656.41	2020	£25,300.52
3	John Smith	2019	Oct	£1,329.16	2020	£51,799.10
4	John Smith	2019	Nov	£36,623.60	2020	£4,070.83
5	Jane Doe	2019	Sep	£43,231.26	2020	£72,141.98
6	Jane Doe	2019	Oct	£58,377.55	2020	£113,679.99
7	Jane Doe	2019	Nov	£175,488.00	2020	£182,891.55
Sheet1

First bring in your table to PQ, filter for date 2019 only. Save to connection
Duplicate the table and filter for 2010 only. Save to connection

Then merge the tables and join as follows:

let
    Source = Table.NestedJoin(Table1, {"Artist", "Month"}, Table2, {"Artist", "Month"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Year", "Sales"}, {"Table2.Year", "Table2.Sales"})
in
    #"Expanded Table2"

 

[sandy666]

another way

Artist	Year	Month	Sales		Artist	Month	2019	2020
John Smith	2019	Sep	34656.41		Anne	Nov		6333.35
John Smith	2019	Oct	1329.16		Anne	Oct		9409.09
John Smith	2019	Nov	36623.6		Anne	Sep		11710.85
John Smith	2020	Sep	25300.52		Jane Doe	Nov	175488	182891.55
John Smith	2020	Oct	51799.1		Jane Doe	Oct	58377.55	113679.99
John Smith	2020	Nov	4070.83		Jane Doe	Sep	43231.26	72141.98
Anne	2020	Sep	11710.85		John Smith	Nov	36623.6	4070.83
Anne	2020	Oct	9409.09		John Smith	Oct	1329.16	51799.1
Anne	2020	Nov	6333.35		John Smith	Sep	34656.41	25300.52
Jane Doe	2019	Sep	43231.26
Jane Doe	2019	Oct	58377.55
Jane Doe	2019	Nov	175488
Jane Doe	2020	Sep	72141.98
Jane Doe	2020	Oct	113679.99
Jane Doe	2020	Nov	182891.55

let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    Pivot = Table.Pivot(Table.TransformColumnTypes(Source, {{"Year", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Source, {{"Year", type text}}, "en-GB")[Year]), "Year", "Sales", List.Sum)
in
    Pivot

 

[sandy666]

or

let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    Pivot = Table.Pivot(Table.TransformColumnTypes(Source, {{"Year", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Source, {{"Year", type text}}, "en-GB")[Year]), "Year", "Sales", List.Sum)
in
    Pivot

with Only Create Connection
then Insert - Pivot Table - Use an External Data Source

with the result

 

[alansidman]

OOPs! I missed Ann. Like Sandy's solution better than mine.

 

[sandy666]

OOPs! I missed Ann. Like Sandy's solution better than mine.

and you added new, not existing category 2010

Duplicate the table and filter for 2010 only.

 

[alansidman]

Sandy,

Getting old is a *****. Having a real Senior Moment, I guess.

 

[sandy666]

Alan, I think it's just one coffee too little

 

[dbethig]

The result I have been tasked with is only one row per artist.

I have made it work with the top 10 Sales so I can make a table next to it with Index/match as the number of rows doesn't change.

There are over 100 Artists so having rows for every month isn't easy to go through.

If this isn't possible I'll have to go for making it a set 200 rows with the index/match table next to the pivot.

 

[dbethig]

The pivot ends at the Margin column in the above example.