Sum of column based on two other columns

[Anonymous]

Hi,

I have the following table,

pat_id	funded amount	status
1	100	Funded
2	100	Funded
3	200	Funded
	400	Funded
	400	Approved
4	100	Approved
5	100	Expired

I want to calculate a measure LoanFunded which is the sum of funded amount for the records having the pat id and status as funded.

I have the following query,

#Loan Funded =

VAR _helpTable =

SUMMARIZE (

    'Table',

    'Table'[LOANS.PAT_ID],

    'Table'[LOANS.FUNDED_AMOUNT]

)

RETURN

if(isnumber(SUMX ( _helpTable, [LOANS.FUNDED_AMOUNT] )),SUMX ( _helpTable, [LOANS.FUNDED_AMOUNT] ), 0)

this is returning loan funded amount for all the statuses.. how do i add the condition, sum it only if the status is "Funded"

Please Help!!

@Ashish_Mathur 

Thanks,

Dharani 

[Ashish_Mathur · Accepted solution]

Hi,

Drag this measure to a card visual

Measure = calculate(sum(Data[funded amount]),data[pat_id]<>blank,data[status]="funded")

Hope this helps.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _table=
FILTER('Table','Table'[pat_id]<>BLANK()&&'Table'[status]="Funded")
return
SUMX(_table,[funded amount])

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur · Accepted solution]

Hi,

Drag this measure to a card visual

Measure = calculate(sum(Data[funded amount]),data[pat_id]<>blank,data[status]="funded")

Hope this helps.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file, if it provides the expected result.

Loan funded measure: = 
SUMX (
    FILTER ( 'Table', 'Table'[pat_id] <> BLANK () && 'Table'[status] = "Funded" ),
    'Table'[funded amount]
)