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.
Hi,
I am quite new to PowerBI, and I want to do something that I am able to do with Excel and Macros
I have one table called SALES
Order,Order_Date,Product,EAN,Categorie,Volume,Price
On the column Order, I have the Order number
On the column Order_Date, I have the date when the order was placed (this is important)
Product is the production description
EAN is the EAN of the product
Volume is the number of piece ordered
Price is the total price for this EAN and this product
I have an other table CATEGORIE :
EAN,Supplier, Categorie, Start_Date, End_Date
On the table, one EAN may appears many time, as its categorie will change from time to time.
For exemple :
EAN1234547899,Nike,Category1, 2018-01-01, 2018-31-01
EAN1234547899,Nike,Category2, 2018-01-02, 2018-28-02
This means that from January 1st to the end of the month, the product will be part of the category 1
And, from february 1st to the end of the month, it will be part of Category2.
To know to which category I have to affect an order, it will depends on the order date.
If 5 orders are placed on january 15th, they will be affected to Categorie 1, and 18 orders placed on february 23, they will be category 2
Now, on my reports, I want to have a vision by Suppliers then by Category.
So it should be :
Nike
Category1 : 5
Category2 : 18
Is there any way to manage that ?
I hope I was clear.
Thanks for your help
Solved! Go to Solution.
Hi @Anonymous,
First cross join SALES table and CATEGORIE table.
Table_1 = FILTER ( CROSSJOIN ( SELECTCOLUMNS ( SALES, "Order", SALES[Order], "Order_Date", SALES[Order_Date] ), CATEGORIE ), [Order_Date] >= [Start_Date] && [Order_Date] <= [End_Date] )
Then, calculate the order number grouped on Suppliers and Category.
Table_2 = SUMMARIZE ( Table_1, Table_1[Supplier], Table_1[Categorie], "Count Order", COUNT ( Table_1[Order] ) )
Best regards,
Yuliana Gu
Hi @Anonymous,
First cross join SALES table and CATEGORIE table.
Table_1 = FILTER ( CROSSJOIN ( SELECTCOLUMNS ( SALES, "Order", SALES[Order], "Order_Date", SALES[Order_Date] ), CATEGORIE ), [Order_Date] >= [Start_Date] && [Order_Date] <= [End_Date] )
Then, calculate the order number grouped on Suppliers and Category.
Table_2 = SUMMARIZE ( Table_1, Table_1[Supplier], Table_1[Categorie], "Count Order", COUNT ( Table_1[Order] ) )
Best regards,
Yuliana Gu
Hi,
Sorry for my late reply. Thanks for your solution.
Regards,
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |