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 (t...
Need Help
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

      % SavedPledge = [SavedPledge]/[CountCancelled]

       

      here is the fomular, thanks. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        and it's "New Measure"

  • parry2k's avatar
    parry2k
    Super User
    9 years ago

    Can you confirm if I understood relation between your tables correctly:

     

    - Contact Table is related to Activity table on contact id 

    - Contact Table is related to pledge table on contact id

     

    To calculate % by month, which date you want to use for month, Created on in Activity table or End Date in Pledge Table?

     

    Also can you confirm if Saved Pledge and Count Cancelled are calculated measure, if yes, can you share their formulas?

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      "SavedPledge" is a "new Measure" in Activity table

      SavedPledge = calculate(counta('Activity - Phone Calls'[Result]),'Activity - Phone Calls'[Result]="Saved Pledge")

       

       

      "CountCancelled" is a "new measure" in Pledge table

      CountCancelled = calculate(counta('Pledge - Rejects and Cancels'[Pledge Status]),'Pledge - Rejects and Cancels'[Pledge Status]="Cancelled")

       

      those 3 tables are connected by "Contact ID"

      Activity table

      Contacts table

      Pledge table

       

      "To calculate % by month, which date you want to use for month, Created on in Activity table or End Date in Pledge Table?" 

      actually i tried on both, but all not working.