Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PEacepip3
Regular Visitor

Summarize Sales & find Best Selling Location

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

Table #1

ProductLocationDate SalesMonthYearSales
A12020-08-01082020200
A22020-07-01072020300
A12020-08-15082020400
A22020-07-15072020500

 

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

LocationSales
1600 (200 + 400)
2800 (300 + 500)

 

And so, our final table would look like this:

Table #3

ProductLocationDate SalesMonthYearSalesBest Location
A12020-08-010820202002
A22020-07-010720203002
A12020-08-150820204002
A22020-07-150720205002

 

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

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors