SWITCH statement not calculating correctly

[saanah2019]

Hi so I have 2 tables with many to many relationship and I have a calculated column which subtracts (correctly) the Customary charge - amount = balance. So I have checked the math and it is correct, now I want to create a new column called Balance =Switch(True(),'Visit'[Balance] = 0, "Fully Paid", 'Visit'[Balance]> 0 && 'Visit'[Balance]< Visit[SumCustomary Charge] , "Partially Paid" , 'Visit'[Balance]= Visit[SumCustomary Charge], "Not Paid", 'Visit'[Balance]<0, "Negative Balance", 'Visit'[Balance] > Visit[SumCustomary Charge], "Amount Greater Than Charge" )

visit table

visit id	customary charge
100	200
200	300
205	150
300	230

payment	amount
100	200
200	203
205	150
300	0

result table should look like this 

visit id	customary charge	amount	balance	tpye
100	200	200	0	Fully paid
200	300	203	97	Partially Paid
205	150	150	0	Fully Paid
300	230	0	230	Not Paid

[v-danhe-msft · Accepted solution]

Hi @saanah2019 ,

Could you have create your [Balance] as a measure? Based on my test, you could refer to below measures:

Balance = CALCULATE(SUM(Visit[customary charge]))-CALCULATE(SUM(Table2[amount]))

type = Switch(True(),'Visit'[Balance] = 0, "Fully Paid", 'Visit'[Balance]> 0 && 'Visit'[Balance]< CALCULATE(SUM(Visit[Customary Charge])) , "Partially Paid" , 'Visit'[Balance]= CALCULATE(SUM(Visit[Customary Charge])), "Not Paid", 'Visit'[Balance]<0, "Negative Balance", 'Visit'[Balance] > CALCULATE(SUM(Visit[Customary Charge])), "Amount Greater Than Charge" )

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He