Merge blank field in calculations?

[Anmolgan]

Hi, I have 2 tables where I am making the relation with a whole number id column, below is the visual that i am trying to build but the problem is that I want to add -1,233.84 balance in all the payer types calculations, the column used for the relationship contains some null values and hence this amount is not getting summed up for all the payer types, is there any way I can add this calcualtion into all the payertypes calculations? The total is correct is just that way of represenation is incorrect for all those payer type.

PFA the below screenshot:

[amitchandak · Accepted solution]

@Anmolgan , I do not see any reason why that number should add. Unless your calculation do not consider it.

[Greg_Deckler]

Can you provide sample data and any formulas you have? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Do you want to add that balance equally across all of the payertypes or is it supposed to be distributed? 

Best method would be to fix the bad data that is causing the blanks.

[Anmolgan]

@Greg_Deckler I want to add the amount equally to all the payer types.

[Greg_Deckler]

Sorry @Anmolgan , just so I am clear, you want to divide the number by the number of payer types and add that amount to each OR you want to add that amount to each of the payer types? Either way it is adding an equal amount but it makes a big difference in the total. I am assuming the 1st option is what you want.

[Anmolgan]

@Greg_Deckler 1st option is the correct.

[Greg_Deckler]

@Anmolgan - OK, is "pcc payments new" a measure or a column? If a measure, please post the formula for it. If a column, are you using a SUM aggregation?

This is going to be more difficult without understanding the source data.

[Anmolgan]

Hi @Greg_Deckler  yes its a calculated column am using below formula

Amount1 = CALCULATE(SUM('dw pcc_ar_transactions'[pcc payments new]), FILTER('dw pcc_ar_transactions', 'dw pcc_ar_transactions'[payer_id]= 0))

not sure what to replace on payer_id

[Greg_Deckler]

OK, forgive me but I'm getting confused. Can you share some example source data so that I can verify what is going on? I don't need a lot, just enough to understand how you are getting to your current result.