Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Calculation between 2 tables

I need to calculate % between 2 different tables, their "date" field is different.

 

it's end up with correct in "total" but wrong in "monthes"

Those 2 tables have 2 different date fields (this may coz the issue) and connected by contact ID. 

 

any1 know how to solve the problem?

Need Help

15 Replies

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    Can u please share the formula which u used the calculation?

     

    Is this measure or Calculated Column ?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      "% Saved Pledge" will be the calulation i would like to create see above picture.  The fomular is "SavedPledge" / "CountCancelled".  For some reason, the calculation isn't correct, you can see above 51/383 is not equal to 49%.  But the Total is correct, 712/4794 is equal to 15%.  

       

      those data are from 2 tables and connected by Contact ID.  see below.  Those 2 date fields are: "Created On" under Activity table and "End Date" under Pledge table. 

       

      • parry2k's avatar
        parry2k
        Super User
        9 years ago

        As Baskar mentioned, can you share your formulat for % calculation, even thou you mentioned how it is calculated but it will be good to see the formula.

         

        Thanks,

        P

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

     

    You can add a calculated column to merge these data files, then use the new column to create the relationship.

     

    Calculcate columns:

    DateKey=  CONCATENATE([Year],[Month])

     

    Calculate the %SavedPledge:

    %SavedPledge= Table1[SavedPledge]/RELATED(Table2[CountCancelled])

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Anonymousmaybe i didn't understand you correctly, i created this calculation field under my "activity" table, but the DAX is invalid. 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi Anonymous,

         

        It seems like your table not direct contain the year and month fields, you can try to use Concatenate(Year([Datefield]),Month([Datefield])) to instead.

         

        If above still not help, can you share me a sample to test?

         

        Regards,

        Xiaoxin Sheng