Calculation between 2 tables

[Anonymous]

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?

[v-shex-msft]

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]

@v-shex-msftmaybe i didn't understand you correctly, i created this calculation field under my "activity" table, but the DAX is invalid. 

 

[v-shex-msft]

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

[Anonymous]

@v-shex-msft thanks, i've created [DateKey] under Pledge table, but gave me below error message when i trying to relationship it with the "created on" date under Activity table. 

how can i share the data with you here? 

[v-shex-msft]

Hi @Anonymous,

Try to create a table which contain the unique value, then create the relationship between above table and the new table.

Table = DISTINCT(UNION(VALUES(Table1[DateKey]),VALUES(Table2[DateKey])))

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft still same error.  how can I attach data to you here?  15% is correct, but rest month % is all wrong. 

[Anonymous]

@v-shex-msft how can i upload the data here?  thanks.  

[v-shex-msft]

Hi @Anonymous,

You can upload to 1dv and share us a link.

Regards,

Xiaoxin Sheng

[Baskar]

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

Is this measure or Calculated Column ?

[Anonymous]

"% 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]

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]

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

[parry2k]

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]

% SavedPledge = [SavedPledge]/[CountCancelled]

here is the fomular, thanks. 

[Anonymous]

and it's "New Measure"