DAX code to determine profit per vehicle.

[nico_1234]

Hello community,

I am faced with the challenge of calculating the profit per vehicle. All attempts end with a syndax error.

For the costs there is the tab "Add1" with the columns: date, amount, vehicle number.

When it comes to sales, things get more complicated because the data is in different tables in an MS SQL database.

There is the table tours, orders and orders_prices.

In the Tours table there are the vehicle number and TourID columns.

In the Orders table there are the columns OrderID, Date and TourID; this is where the connection between the tours and orders takes place.

In the Orders_Prices table there are the OrderID and Prices columns. There are several lines with an order ID and different prices. Because several price types with values are stored in an order.

All three tables are related in Power BI, the sales are correctly displayed per vehicle in a chart. This works wonderfully. In order to filter the date in the chart, I created my own date table.

Can someone help me with the DAX code on how to calculate the sales per vehicle minus the costs per vehicle? In order to filter the date in the chart, I created my own date table.

I would like to thank you very much in advance for any help that leads me to the solution!

greeting

Nico

[Anonymous]

Hi @nico_1234 ,

Please try to create a measure.

Profit Per Vehicle = 
CALCULATE(
    SUM(Orders_Prices[Prices]) - SUM(Add1[amount]),
    FILTER(
        ALL(Orders),
        Orders[TourID] = Tours[TourID]
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[nico_1234]

Hi Nekko,

thank you very much for your suggested solution. Unfortunately this didn't work 😕

I made an example pbix. 

Here is the link to the file: https://www.transfernow.net/dl/20231115Wwb5hRTt

Thank you in advance for your effort and help!

Greetings

Nico

[nico_1234]

Can someone help me? 🙂