Filter in calculate( )

[rickylee]

Hello,

In my table, I have a "Total order billing (include refund)" which is the amount for a transaction. I also have a column "refund" which contains either True or False only.

To find out the total amount when the refund column is set to True, I have the following measure: 

Total refund = CALCULATE([Total order billing (include refund)], salesorders[refund]="True") 

Surprisingly, the above formula does not work, I have to resort to this one:

Total refund = CALCULATE([Total order billing (include refund)], filter(ALL(salesorders[refund]),SEARCH("True",salesorders[refund],1,0)))

The second formula is a search which takes long. Is there any reason why I cannot use the first formula?

Please notice Total order billing (include refund) is a measure of: 
Total order billing (include refund) = SUM(salesorders[totalamount])

[rickylee]

Hello @PattemManohar,

I have form the following sample table:

Order     Billing    Refund

1	100	TRUE
2	200	FALSE
3	300	FALSE

There are two meausures:
Total billing = SUM(Test[Billing])

Total refund amount = CALCULATE([Total billing], Test[Refund]="True")

If you try to display "Total refund amount", it does not work. However, I think the calculate() function is used correctly.

Thanks,
Ricky

[PattemManohar · Accepted solution]

@rickylee Please try this, if your "refund" field is Text Data type

Total refund amount = CALCULATE(SUM([Total billing]), Test[Refund]="True")

If "refund" field is Boolean data type then use 

Total refund amount = CALCULATE([Total billing], Test[Refund]=True())

[venug20]

@rickylee

You can use below formula to get values.....

Refunamt = CALCULATE(SUM('Billing-Refund'[Billing]), 'Billing-Refund'[Refund]=TRUE())

If it is soultion for your query, Pls accept as solution. It is helpful to others....

[Zubair_Muhammad]

@rickylee

Is the Refund column a text data type column?

What if you use TRUE()

Total refund amount = CALCULATE([Total billing], Test[Refund]=True())

[PattemManohar]

@rickylee Could you please post sample data for testing your scenario.