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'm trying to create a new calculated table from 3 existing tables that has 1 row for each date in my range, each client I have, each product that each client normally buys and the highest single order quantity that they bought each day. For example, if they bought 2 on one order and 3 on another, i'd want it to display 3. If no record of sales are found for that client and product on a given day, it should show that they bought 1 (this is a weird hypothetical, but just go with me). I'm struglling with how to setup the calculated table and the existing table joins.
Here are the tables I'm starting with:
A table of clients and the products that each one buys. You'll see that not all clients buy all products:
Client | Product |
A | X |
A | Y |
A | Z |
B | X |
B | Y |
C | X |
A table of dates. To make this example easy, I'll just use 2 dates
Date |
4/18/2018 |
4/19/2018 |
A table of sales. Not every client buys every product on every day, but they can place multiple orders for the same product on each day.
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 |
Here is the output I'm looking to get:
Note, that for each day each client and each product in the client/product table is listed. If they didn't buy that product on that day, it lists a 1. If they did, it lists the largest order that they placed for that product on that day.
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.
I think I may have something for you.
Create a calculated table, with a crossjoin of date and client-products. Then you have all combinations of dates, clients and products.
Output Table = CROSSJOIN('Clients-Products', 'Dates')
Create a column in this new Output Table, which looks up the right value in the Sales table, by filtering on Date, Client and Product.
Largest Order = CALCULATE( IF(ISBLANK(MAX(Sales[Qty Sold])), 1, max(Sales[Qty Sold])), FILTER( Sales, Sales[Date] = 'Output Table'[Date] && Sales[Client] = 'Output Table'[Client] && Sales[Product] = 'Output Table'[Product]
) )
Then you get this:
So I just used your Date table, the Client-Peroducts table and the Sales table. No extra relationships created.
Hope this helps.
I think I may have something for you.
Create a calculated table, with a crossjoin of date and client-products. Then you have all combinations of dates, clients and products.
Output Table = CROSSJOIN('Clients-Products', 'Dates')
Create a column in this new Output Table, which looks up the right value in the Sales table, by filtering on Date, Client and Product.
Largest Order = CALCULATE( IF(ISBLANK(MAX(Sales[Qty Sold])), 1, max(Sales[Qty Sold])), FILTER( Sales, Sales[Date] = 'Output Table'[Date] && Sales[Client] = 'Output Table'[Client] && Sales[Product] = 'Output Table'[Product]
) )
Then you get this:
So I just used your Date table, the Client-Peroducts table and the Sales table. No extra relationships created.
Hope this helps.
Thanks Edgar! Worked perfectly.
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |