Forum Discussion

JC-80's avatar
JC-80
New Member
10 years ago
Solved

Multiply Columns within different Tables - Not giving correct output...

Hi,    I am hoping someone can assist.    I have 2 tables of data Table A has all the data contained within it and Table B has unique fields - the relationship is Many to One.   Table A has Col...
Need Help
  • JC-80's avatar
    JC-80
    10 years ago

    In this example I managed to fix the issue. 

     

    I completed a merge on the 2 tables with the relevant data. This created a column that contained all of the columns from 'Table 2', so I picked 'Hourly Rates'. I then created a calculated column multiplying the 'Hours' column by the 'Hourly Rates' column, this gave me the desired outcome. 

     

    Thanks for everyones suggestions and help. :smileywink:

     

     

JC-80's avatar
JC-80
New Member
10 years ago

Thank you for the quick response. 

 

I think I understand what you are suggesting but I am not clear on how I can remedy this issue? Is there an alternative solution to get the right result to show? 

 

I need the total, as I want to be able to show the cost against either the project, month or person. This is vital to the report. 

 

Thanks 

JC-80's avatar
JC-80
New Member
10 years ago

Just to add, I have looked at some of the calculation and they dont even seem to be doing that...

 

 

 Correct Calcualtion  Power BI Calculation
 HoursHourly RateCost  HoursHourly RateCost
 1 £       118.57 £        118.57  1£118.57£118.57
 1 £       118.57 £        118.57  1£118.57£118.57
 2 £       118.57 £        237.14  2£118.57£237.14
 2.5 £       118.57 £        296.43  2.5£118.57£296.43
 0.5 £         62.14 £          31.07  0.5£62.14£31.07
 1 £         62.14 £          62.14  1£62.14£62.14
Total8£598.56£863.92 Total8£180.71£1,445.68

 

It looks as though the calculation (according to Power BI), is taking the total number of hours and multiplying it by the unique values within the hourly rate (£118.57 and £62.14 = £180.71). All I need is the total of the cost (£863.92). 

 

Could this be due to the relationship between the 2 tables? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Hi JC-80,

    Could you please post the sample data of your two tables and post the expected result here?  Regrading to the above table, you can simply create a measure to summarize the cost by using the formula: Measure = SUMX(Table1,Table1[Hours]*Table1[Hours Rate]).




    Thanks,
    Lydia Zhang

    • JC-80's avatar
      JC-80
      New Member
      10 years ago

      In this example I managed to fix the issue. 

       

      I completed a merge on the 2 tables with the relevant data. This created a column that contained all of the columns from 'Table 2', so I picked 'Hourly Rates'. I then created a calculated column multiplying the 'Hours' column by the 'Hourly Rates' column, this gave me the desired outcome. 

       

      Thanks for everyones suggestions and help. :smileywink: