Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
deasine
New Member

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)

  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)

 

4 REPLIES 4
deasine
New Member

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)

 

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
Post Patron
Post Patron

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? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.