New User: Using a calculated measure between different tables

[deasine]

Hi Everyone,

I'm quite new to PowerBI so I'm still trying to get the hang of it. 

My question is: How we can do a measure between different tables? I can obtain the revenue  

What I'm trying to achieve:

• Graph of Average Transactions Per Customer by Month i.e. [2-4]/[1-2] for each Month (MMM) along X-axis
• Graph of Average Reveue Per Customer by Month i.e. [2-5]/[1-2] for each Month (MMM) along X-axis

My datasets available (I may be linking the wrong sets? - Hope this is descriptive enough to follow)

1. Customers:
   
   1. Month-Year (in MMM-YY)
   2. Customers (Total customers per month)
2. Transactions and Revenues
   1. Transaction Start Date/Time (in DD/MM/YY HH:MM format)
   2. Transaction End Date/Time (in DD/MM/YY HH:MM format)
   3. Transaction Duration
   4. Transaction Unit (all transactions set as 1)
   5. Total Revenue of the corresponding transation
3. Dynamic DAX Calendar (self created)
   • Date (DD/MM/YY format) - This is linked to 2-1 above
   • YearPeriod (YYYYMM format based on date)
   • Month (MMM format based on date) - This is linked to 1-1 above
   • MonthYear (MMM-YY format based on date)

[deasine]

Hi Everyone,

I'm quite new to PowerBI so I'm still trying to get the hang of it. 

My question is: How we can do a measure between different tables? I can obtain the revenue  

What I'm trying to achieve:

• Graph of Average Transactions Per Customer by Month i.e. [2-4]/[1-2] for each Month (MMM) along X-axis
• Graph of Average Reveue Per Customer by Month i.e. [2-5]/[1-2] for each Month (MMM) along X-axis

My datasets available (I may be linking the wrong sets? - Hope this is descriptive enough to follow)

1. Customers:
   
   1. Month-Year (in MMM-YY)
   2. Customers (Total customers per month)
2. Transactions and Revenues
   1. Transaction Start Date/Time (in DD/MM/YY HH:MM format)
   2. Transaction End Date/Time (in DD/MM/YY HH:MM format)
   3. Transaction Duration
   4. Transaction Unit (all transactions set as 1)
   5. Total Revenue of the corresponding transation
3. Dynamic DAX Calendar (self created)
   • Date (DD/MM/YY format) - This is linked to 2-1 above
   • YearPeriod (YYYYMM format based on date)
   • Month (MMM format based on date) - This is linked to 1-1 above
   • MonthYear (MMM-YY format based on date)

[deasine]

Please delete - somehow got posted twice! Thanks.

Link to other post: https://community.powerbi.com/t5/Desktop/New-User-Using-a-calculated-measure-between-different-table...

[nmck86]

Have you joined all the tables by date yet?

[deasine]

Hi nmck86, 

I did it through YearPeriod rather than date as my data output on customers was done on a total per month basis (in YearPeriod form).

Somehow, I removed all links and put everything again and it seemed to work, but I ran into another problem. I'm trying to get average transactions/revenue per customer per month, I'm getting weird figures (I did an xlsx check to make sure). 

• My [Revenue] is per transaction basis, so I did a Measure = .TotalRevenue = SUM([Revenue]) -
  • My original [Revenue] data is a Σ  stored as a data type for whole number with $ 
  • This measure was created as I couldn't seem to directly divide revenue/transactions over custome
• My [Customers] is per month basis, so I did a Measure = Sum ([Passengers])
  • My customers [customer] data is a Σ  stored as a data type for whole number
  • This measure was created as I couldn't seem to directly divide revenue/transactions over customer
  • Note that this is stored as a separate table dataset from the revenue/trasnactions
• I have another Measure /Total Revenue Per Customer = DIVIDE([.TotalRevenue],[.Total Customers],"N/A"
  • For this measure, I couldn't simply divide individual revenues over total customers
  • My desired output is to have months across X-axis, transactions/revenue per customer over Y-Axis

Am I doing my measurements incorrectly?