Forum Discussion

RPATER's avatar
RPATER
Frequent Visitor
5 years ago

DAX Calculate Costs per send using Relationships between two tables

Hello All,   I have two tables(a Costs table, and a Sends table).  The "Costs" table contains Date, Vendor Name and Cost.  The "Sends" table contains(Date, Vendor Name, # of Sends).  The Cost of se...
CALCULATE
DAX
Measures
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

RPATER , Create a common date table and join both tables with date table and use the measure with date dim or without any common parameter

 

divide(sum(Cost[Cost])/sum(Sends[Sends))

RPATER's avatar
RPATER
Frequent Visitor
5 years ago

Hi amitchandak ,

 

I am having trouble making that solution work.  The reason being is I think I need a fixed measure for Cost per send that is set to the specific Vendor and Month.

 

For Example:

Vendor 1 has 266,974 total sends in April.  The cost for Vendor 1 in April is $66,666

$66,666 / 266,974 is $0.25 per send for Vendor 1 in April.  I would want to use that measure and multiple it by each days sends:

 

4/1 - 240 sends * $0.25 = $60

4/2 - 332 sends * $0.25 = $83

 

Currently, with the common date table...the "Cost per send" changes with every date in the table.

 

Does this make sense?

 

Thank You,

Ryan