cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamesFPower
New Member

Calculate Cost per Product SQL

Hello! 

 

If this has been asked before I am sorry but I did have a good look and I couldnt find anything on this, but I sure bet it has been asked before.

 

So I will do my best to explain the siutation, our SQL database has: 

db_contracts.[contract]

db_product.[unit_cost]

db_sales.[units]

 

I need help working out how do calculate the total cost of the contracts

filter: Contact

totalcost = db_sales.[units]*db_product.[unit_cost]

 

 

 

1 ACCEPTED SOLUTION

Hello @JamesFPower 

 

and how are they connected? However you need to create a measure that looks like this

SUM(Product[Costs])*SUM(Sales[Units])

and in the visual you put the contract n. and this measure. But as I already said, all tables need to be connected first.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

3 REPLIES 3
Jimmy801
Super User III
Super User III

Hello @JamesFPower 

 

use SUMX for this. Here a code that should work out for you

SUMX(YOURTABLE, db_sales.[units]*db_product.[unit_cost])

Are these fields both in the same table I suppose. What you mean with filter? In case apply a Filter to your table like FILTER(YOURTABLE, [Contract]="abc")


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi Jimmy, 

 

Thanks for the quick reply. 

 

Sadly they are not in the same table, each of the fields above are all in their own tables.

We have a table of contracts this is what we want to filter on (i.e we want to see the cost of X contract) , a table of products that contains how much each product costs (unit_cost) and a table of sales that contains the number of units sold (units)

 

Capture.PNG

 

Thanks

Hello @JamesFPower 

 

and how are they connected? However you need to create a measure that looks like this

SUM(Product[Costs])*SUM(Sales[Units])

and in the visual you put the contract n. and this measure. But as I already said, all tables need to be connected first.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors