Forum Discussion
New User: Using a calculated measure between different tables
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)
- Customers:
- Month-Year (in MMM-YY)
- Customers (Total customers per month)
- Transactions and Revenues
- Transaction Start Date/Time (in DD/MM/YY HH:MM format)
- Transaction End Date/Time (in DD/MM/YY HH:MM format)
- Transaction Duration
- Transaction Unit (all transactions set as 1)
- Total Revenue of the corresponding transation
- 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)
4 Replies
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)
- Customers:
- Month-Year (in MMM-YY)
- Customers (Total customers per month)
- Transactions and Revenues
- Transaction Start Date/Time (in DD/MM/YY HH:MM format)
- Transaction End Date/Time (in DD/MM/YY HH:MM format)
- Transaction Duration
- Transaction Unit (all transactions set as 1)
- Total Revenue of the corresponding transation
- 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)
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-tables/m-p/630126
Have you joined all the tables by date yet?
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?
- My [Revenue] is per transaction basis, so I did a Measure = .TotalRevenue = SUM([Revenue]) -
