cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowwerBI
Regular 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
ladyhaley
Resolver I
Resolver I

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
ladyhaley
Resolver I
Resolver I

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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors