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 sends per month for each vendor differs by Vendor and, in some cases, Month.  I am trying to calculate an "Actual Cost" measure that calculates the cost per send in specific months for each Vendor:

 

Cost[Vendor Name] = Sends[Vendor Name] && Month(Cost[Date]) = Month(Sends[Date])

 

Then

Cost[Cost]/Sends[Sends] = Costs per Send

 

Costs Table Example:

Vendor NameDateCost per Month
Vendor11/1/2021$66,666.67
Vendor12/1/2021$66,666.67
Vendor13/1/2021$66,666.67
Vendor14/1/2021$66,666.67
Vendor15/1/2021$66,666.67

Vendor2

1/1/2021$1,500
Vendor22/1/2021$1,500
Vendor23/1/2021$1,500
Vendor24/1/2021$1,500
Vendor25/1/2021$1,500
Vendor31/1/2021$90,473
Vendor32/1/2021$19,326
Vendor33/1/2021$13,293
Vendor34/1/2021$13,386
Vendor35/1/2021$18,766
Vendor41/1/2021$0
Vendor42/1/2021$0
Vendor43/1/2021$0
Vendor44/1/2021$0
Vendor45/1/2021$0

 

Sends Table Example:

DATEVENDOR NAMESENDS
4/1/2021Vendor3240
4/1/2021Vendor244
4/1/2021Vendor1332
5/1/2021Vendor31
5/1/2021Vendor225
5/1/2021Vendor14708
6/1/2021Vendor317
6/1/2021Vendor2592
6/1/2021Vendor1192

 

Please let me know if you need anymore information!

 

Thank You,

Ryan

CALCULATE
DAX
Measures

2 Replies

  • 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