Calculated Table

[Anonymous]

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

[Ashish_Mathur]

Hi,

You may refer to my solution in this workbook.

Hope this helps.

[Anonymous]

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:

https://community.powerbi.com/t5/Desktop/DAX-table-with-all-dates-products-clients-and-largest-purch...

[Greg_Deckler]

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. 

[Anonymous · Accepted solution]

Thanks Greg! It didn't get me quite where i needed to be, but close. For reference, the solution is here:

https://community.powerbi.com/t5/Desktop/DAX-table-with-all-dates-products-clients-and-largest-purch...