Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to create a caltualated summary that lists each date in my date table, each client and product that that client buys from my Client Products table and the highest single order quantity from my sales table. If the client didn't buy the product that day, list 1.
So in other words, my 3 existing tables are:
Client Products | |
Client | Product |
A | X |
A | Y |
A | Z |
B | X |
B | Y |
C | X |
Dates |
Date |
4/18/2018 |
4/19/2018 |
Sales | |||
Date | Client | Product | Qty Sold |
4/18/2018 | A | X | 2 |
4/18/2018 | A | X | 3 |
4/18/2018 | A | Z | 2 |
4/18/2018 | B | X | 2 |
4/18/2018 | B | Y | 2 |
4/18/2018 | C | X | 2 |
4/19/2018 | A | Z | 3 |
4/19/2018 | A | Z | 4 |
And here's the output i want:
New Table | |||
Date | Client | Product | Largest Order |
4/18/2018 | A | X | 3 |
4/18/2018 | A | Y | 1 |
4/18/2018 | A | Z | 2 |
4/18/2018 | B | X | 2 |
4/18/2018 | B | Y | 2 |
4/18/2018 | C | X | 2 |
4/19/2018 | A | X | 1 |
4/19/2018 | A | Y | 1 |
4/19/2018 | A | Z | 4 |
4/19/2018 | B | X | 1 |
4/19/2018 | B | Y | 1 |
4/19/2018 | C | X | 1 |
Solved! Go to Solution.
Thanks Greg! It didn't get me quite where i needed to be, but close. For reference, the solution is here:
Hi,
You may refer to my solution in this workbook.
Hope this helps.
Thanks Ashish! Unfortunately I couldn't use the Dates table in the data model like you did in Excel because the Dates table was actually a calculated table. I probably should have specified that. For reference the solution is here:
Try this:
New Table = SUMMARIZE(Sales,[Date],[Client],[Product],MAX([Qty Sold]))
BTW, excellently formed question with sample data that can be copied and pasted as well as expected output. Well done.
Thanks Greg! It didn't get me quite where i needed to be, but close. For reference, the solution is here: