How to calculate total sales

[Anonymous]

Hi! I am pretty new to Power Bi and I am having troubles calculating the total sales with a measure.

Basically the data model is that the customers have multiple properties and they can book multiple cleaning services for their properties. Each cleaning service has a different price cost.

I was able to show a visual of the sales for each service by creating a measure.

However I would also like to show the total of the sales which would be 5139 € in another visual if not possible to show it in the same visual. So basically I want to be able to show the sales for each service/category and the total sales.

Here is the sample file: https://1drv.ms/u/s!AuHkRkg43pTjgXV7O0QSG3rfwBZe?e=OjAJEj

I really appreciate the help!

Thank you!

[Anonymous · Accepted solution]

I solved the number of customers with the countx measure.

[Anonymous]

@AllisonKennedy  omg thank you! it works 🙂 you are a life saver

[AllisonKennedy]

@monacharline21 You can keep order and order details in separate tables. This is good. Simply use the RELATED() function to get the price in the order details table.

Try this measure:

Total Sales: SUMX(T_Auftragsdetails, RELATED(D_Dienstliestugen[Preis]))

I'm sorry about my spelling - I hope it makes sense and help.

[Anonymous]

@AllisonKennedy Thank you!

would you by any chance also know how to show the number of customers? If I use the measure count(T_Kunden[K_Nummer]) it says 16. Which is basically correct as a total. But if I choose a certain service in the filter/slicer it sticks to 16 and does not change. But for instance if I choose Gartenpflege I want to see the number of customers who purchased this service. 

[AllisonKennedy]

@Anonymous  Maybe try 

Count Customers = CALCULATE(COUNTROWS(T_Kunden), SUM(T_Auftragsdetails[ATD_D_Nummer] > 0))

Again, check my spelling (apologies)

[Anonymous]

ok thank you. But if I include the order details meaning the service number in the orders table I am only able to put in one service for each order. Or can the same order number be included twice in the table?

Thank you!

Is there no way to work with the current model and calculate the total sales?

[daxer-almighty]

First and foremost, you should look at how to properly model data where there are orders and order details. Sadly, the pattern for doing this is not present at www.daxpatterns.com. This is unfortunate as it only is explained in Marco and Alberto's courses on DAX. But I'll give you a hint: you should not separate the Orders table from Order Details. They should be contained in one single fact table.

Exactly the same applies to Properties and Property Details. You should unify the two tables.