Forum Discussion

Frequent Visitor

3 years ago

Solved

Calculation with multiple filters

I need to calculate:

When Payment_Type = 1

AND Service_Type = 1

Then multiple Payment_Type by Year_Rate

When I try:

Payment = IF(

AND(

COUNT(FILES_PAYMENT[Service_Type])= 01,

COUNT(FILES_PAYMENT[Payment_Type])= 1),

CALCULATE(

SUM(RATES[Year_Rate])*COUNT(FILES_PAYMENT[Payment_Type])))

It does not filter out the other Payment_Type and is not calculating the total payment for Service_Type of 01.

tberr86
Frequent Visitor
3 years ago
Try:
Payment= IF(AND(FILES_PAYMENT[Service_Type]= 1,FILES_PAYMENT[Payment_Type]= 1),[Year_Rate]*[Payment_Type])

You may run into errors depending on data type as this function assumes a number data type not a string.
FreemanZ
Super User
3 years ago
hi S_Berg
Could you help paste some sample data to help those who might be able to help you?
v-zhangti
Community Support
3 years ago
Hi, S_Berg

You can try the following methods.

Payment = CALCULATE ( SUM ( RATES[Year_Rate] )) * CALCULATE ( COUNT ( FILES_PAYMENT[Payment_Type] ), FILTER ( ALL ( FILES_PAYMENT ), [Payment_Type] = 1 && [Service_Type] = 1 ) )

If that doesn't resolve your issue, provide sample data and expected output.

Best Regards,

Community Support Team _Charlotte

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