Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |