Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe 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!
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:
That will give you the Sales from the Best Location which you will be able to filter by date, customer, year etc
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |