Summarize Sales & find Best Selling Location

[PEacepip3]

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

[mahoneypat · Accepted solution]

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

[PEacepip3]

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!

[Anonymous]

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