Forum Discussion

PowwerBI's avatar
PowwerBI
Frequent Visitor
5 years ago
Solved

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):

 

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

 

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.:

 

 

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

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

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

     

    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]

     

     

    Out come: 

    EDIT:

    You can turn of Row Subtotals to remove the totals 

     

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

     

    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]

     

     

    Out come: 

    EDIT:

    You can turn of Row Subtotals to remove the totals 

     

     

     

     

    • PowwerBI's avatar
      PowwerBI
      Frequent Visitor
      5 years ago

      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!