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
PowwerBI
Frequent Visitor

Calculating values based on independent table

Hi, I am rather new to power BI and I do not know if this is possible but I would like to solve the following problem. I have two tables.

Table 1, transactional data (example data, one entry per purchase):

PowwerBI_0-1614120872149.png

 

Table 2, partially aggregated data (example data, e.g. distance per month, aggregated per vehicle and month):

PowwerBI_1-1614120884926.png

 

Expected result: Table 3 (I know that there are 2 owners of vehicle ID002 but let's assume that owner and vehicle are unique in month), the problem ist of course to add the distance to the owner / vehicle and if applicable aggragate it for month/year etc.:

PowwerBI_2-1614120901851.png

 

 

Is there a way how to achieve it? Do I need to add a measure to table 1? Thank you!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

hi to achieve this i loaded in your data and also create bridge tables like so 

Capture.PNG

 

Calendar table can be create with dax using Modeling > New Table

 

Calendar = ADDCOLUMNS ( CALENDAR (MIN('Table 1'[Date]), MAX('Table 1'[Date])), "Week Number", FORMAT ( [Date], "ww" )) ***you can add other colums to fit needs**

 

Vehicle can also be made with follow code, you can reference which ever table has more IDs or if you have a list of known IDs you can load it in: 

 

VEH = DISTINCT('Table 1'[Veh]) 

 

 

I then use matrix visual with the following fields

 

ROWS:
'Table1'[Owner]
'VEH'[VEH]
COL:
'Calendar'[Date]
VAL:
'Table1'[Price]
'Table2'[Distance]

 

ladyhaley_0-1614123159841.png

 

Out come: 

ladyhaley_1-1614123179659.png

EDIT:

You can turn of Row Subtotals to remove the totals 

ladyhaley_3-1614123333054.png

 

 

 

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

hi to achieve this i loaded in your data and also create bridge tables like so 

Capture.PNG

 

Calendar table can be create with dax using Modeling > New Table

 

Calendar = ADDCOLUMNS ( CALENDAR (MIN('Table 1'[Date]), MAX('Table 1'[Date])), "Week Number", FORMAT ( [Date], "ww" )) ***you can add other colums to fit needs**

 

Vehicle can also be made with follow code, you can reference which ever table has more IDs or if you have a list of known IDs you can load it in: 

 

VEH = DISTINCT('Table 1'[Veh]) 

 

 

I then use matrix visual with the following fields

 

ROWS:
'Table1'[Owner]
'VEH'[VEH]
COL:
'Calendar'[Date]
VAL:
'Table1'[Price]
'Table2'[Distance]

 

ladyhaley_0-1614123159841.png

 

Out come: 

ladyhaley_1-1614123179659.png

EDIT:

You can turn of Row Subtotals to remove the totals 

ladyhaley_3-1614123333054.png

 

 

 

 

Thank for the quick answer! I will test it and implement it tomorrow (the real tables are much more complex and rather large - hundreds of millions entries) and I will lat you know.

 

EDIT: It works well, Thanks a lot!

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.