topic Re: Summarize Sales & find Best Selling Location in DAX Commands and Tips

Summarize Sales & find Best Selling Location

PEacepip3 — Wed, 26 Aug 2020 22:36:41 GMT

I have the following table for different products sold in different locations:

Table #1

Product	Location	Date Sales	MonthYear	Sales
A	1	2020-08-01	082020	200
A	2	2020-07-01	072020	300
A	1	2020-08-15	082020	400
A	2	2020-07-15	072020	500

I'm trying to identify which was the best location to sell over the year.

So, for this examples, if I summarize how much we sell in each location we would get:

Table #2

Location	Sales
1	600 (200 + 400)
2	800 (300 + 500)

And so, our final table would look like this:

Table #3

Product	Location	Date Sales	MonthYear	Sales	Best Location
A	1	2020-08-01	082020	200	2
A	2	2020-07-01	072020	300	2
A	1	2020-08-15	082020	400	2
A	2	2020-07-15	072020	500	2

However, I don't find the way to calculate that new column.

I have tried this so far:

Summarize(sales, sales[MonthYear], sales[Product], sales[Location], "Best Location", SUM(sales[Sales]))

Which would give me basically Table #2.

However, I don't know how to put the Location name into the Best Location column.

I believe I would need to match the Location columns between the Original Table and the new Summarized Table.. or there might be a simplier approach to this.

Thanks

Re: Summarize Sales & find Best Selling Location

mahoneypat — Wed, 26 Aug 2020 23:13:02 GMT

Instead of making a new summarized table, you can use this expression in a calculated column on Table1

Best Location = VAR thisproduct = Sales[Product] VAR summary = ADDCOLUMNS ( SUMMARIZE ( Sales, Sales[Product], Sales[Location] ), "@sales", CALCULATE ( SUM ( Sales[Sales] ), ALLEXCEPT ( Sales, Sales[Location], Sales[Product] ) ) ) VAR maxthisproduct = MAXX ( FILTER ( summary, Sales[Product] = thisproduct ), [@sales] ) RETURN MAXX ( FILTER ( summary, Sales[Product] = thisproduct && [@sales] = maxthisproduct ), Sales[Location] )

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Summarize Sales & find Best Selling Location

PEacepip3 — Thu, 27 Aug 2020 00:00:07 GMT

Amazing:)

This was indeed out of my reach at the moment but it is great to have an idea of what can be done in the future with DAX.

Thanks!

Re: Summarize Sales & find Best Selling Location

Anonymous — Thu, 27 Aug 2020 03:21:06 GMT

I would have approached this slightly differently as I prefer to uses measures over calculated columns.

I would use TOPN to achieve the result, If you do not already have a Total Sales measure create that by summing up your sales QTY column as follows: Total Sales = SUM(Sales[Sales]).

Then create your Best Location measure as follows:

Best Location by Sales Volume =

VAR BestLocation = CALCULATETABLE ( TOPN ( 1, CALCULATETABLE ( VALUES ( 'Sales'[Location ) ), [Total Sales] ), ALLSELECTED() )

RETURN

CALCULATE (

[Total Sales],

KEEPFILTERS (BestLocation)

)

That will give you the Sales from the Best Location which you will be able to filter by date, customer, year etc