BUY and SALES per seller

[olivier42]

Hello community,
I am French and I will try to pose my problem in English to get the most support.
I use a DATE table, a SELLERS table and a VEHICLES table
Each new vehicle file is unique and is identified by a management code.
I have an active relationship between the VENDORS table and VEHICLES table, on the SELLER and SELLER_SUPPLIER fields
and an inactive relationship between SELLER and SELLER_CUSTOMER.
A seller orders a vehicle from a supplier and sells this vehicle to a customer, which means that the name of the seller can be different: I therefore have for a single file a SELLER_SUPPLIER field and another SELLER_CUSTOMER field. Example: seller A buys the vehicle from a supplier and seller B sells it to a customer.
My problem is as follows: how to obtain the number of supplier orders per seller and how to obtain the number of sales to customer?
Because for the moment when I select seller B the CA is false because the vehicle files purchased by seller A are missing
THANKS

[AnalyticPulse]

To achieve this in Power BI, you can use DAX to create calculated columns or measures in your data model. Here's a step-by-step guide:

1. Create Relationships: Ensure that there are relationships between your DATE table and the VEHICLES table. There should also be relationships between the SELLER_SUPPLIER and SELLER_CUSTOMER fields in the VEHICLES table and the respective seller tables.
2. Create Calculated Columns for Seller Type: You can create a calculated column in your VEHICLES table to determine the seller type for each record. For example, you can use the following DAX formula:
   SellerType = IF(ISBLANK([SELLER_SUPPLIER]), "Customer", "Supplier")
   This formula checks if the SELLER_SUPPLIER field is blank. If it is, then the SellerType is "Customer"; otherwise, it is "Supplier".
3. Create Measures for Count of Orders: Now, you can create measures to count the number of supplier orders and customer sales. For the number of supplier orders, you can use:
   SupplierOrders = CALCULATE(COUNTROWS(VEHICLES), VEHICLES[SellerType] = "Supplier")
   For the number of customer sales, you can use:
   CustomerSales = CALCULATE(COUNTROWS(VEHICLES), VEHICLES[SellerType] = "Customer") These measures use the CALCULATE function to filter the VEHICLES table based on the SellerType.
4. Visualize the Data: Drag and drop the SupplierOrders and CustomerSales measures into your Power BI report to visualize the counts.

Here's a quick overview of the DAX functions used:

• ISBLANK: Checks if a value is blank.
• CALCULATE: Modifies the filter context in which a formula is evaluated.

Adjust the column and measure names based on your actual column names, and make sure your relationships are set up correctly. This approach allows you to dynamically calculate the counts based on the seller type for each record.

If this helped, Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
https://instagram.com/analytic_pulse
https://analyticpulse.blogspot.com/

subscribe to Youtube channel For fun facts:
https://www.youtube.com/@CogniJourney