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:

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

I *believe* what you are seeing in the Total row is that the measure is essentially not contextually filtered. So, in the total row, it is summing all of your values for all of your base hourly rates and then multiplying that by a sum of all of your hours worked.

 

Essentially:

 

Instead of: ($125 * 16) + ($150 * 16) = $4,400

 

you are getting:

 

($125 + $150) * (16 + 16) = $8,800

 

Remember, a measure's value is controlled by its context and in the Total row, it is removing all of the context essentially and you are getting a wrong answer as a result.

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: