Forum Discussion
Helper IIIFilter in calculate( )
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 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())
5 Replies
- PattemManohar
Community Champion
rickylee Could you please post sample data for testing your scenario.
rickyleeHello PattemManohar,
I have form the following sample table:
Order Billing Refund1 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- Zubair_Muhammad
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 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())
