Set Analysis for calculating 3 different values

[cliff_clayman]

I am trying to get a total cost of purchases by type.  I have 3 types.  Here is the expression that works to get the total for just one type.

=Money(Count(DISTINCT If(FY=vCFY and [PurchaseType] = 'ABC', [PO]))*vPOCostABC,'$#,##0')

How can I change this to Set Analysis and account for two more PurchaseType values of 'DEF' and 'XYZ'?

[sunny_talwar]

May be like this:

=Money(Count(DISTINCT {<FY = {$(=vCFY)}, [PurchaseType] = {'ABC', 'DEF', 'XYZ'}>} [PO]) * vPOCostABC,'$#,##0')

[cliff_clayman]

I failed to mention that there are 3 Cost variables as well that go along with each PurchaseType.  Each PurchaseType value has a different cost associated with it.

PurchaseType     Cost Variable

ABC                    vPOCostABC

DEF                    vPOCostDEF

XYZ                    vPOCostXYZ

[neelamsaroha157]

Hey Cliff try this out

Count(Distinct {<FY = {'$(vCFY)'}, [PurchaseType] = {'ABC', 'DEF', 'XYZ'}>} [PO]*vPOCost)

Let me know if it works..

[sunny_talwar]

Is Purchase Type one of the dimensions? If it is, then you can try this

=Money(Count(DISTINCT {<FY = {$(=vCFY)}, [PurchaseType] = {'ABC', 'DEF', 'XYZ'}>} [PO]) *

Pick(Match([PurchaseType], 'ABC', 'DEF', 'XYZ'), vPOCostABC, vPOCostDEF, vPOCostXYZ)

,'$#,##0')

[cliff_clayman]

PurchaseType is not a dimension.  This expression is just in a text box.

[sunny_talwar · Accepted solution]

Then may be this:

=Money(Sum(Aggr(Count(DISTINCT {<FY = {$(=vCFY)}, [PurchaseType] = {'ABC', 'DEF', 'XYZ'}>} [PO]) *

Pick(Match([PurchaseType], 'ABC', 'DEF', 'XYZ'), vPOCostABC, vPOCostDEF, vPOCostXYZ), PurchaseType))

,'$#,##0')

[cliff_clayman]

That did it...thanks once again!!!