Calculate IF using sum and filter.

[jpbi23]

I have current Dax formula, 

Month 1 Recurring = 

CALCULATE(

    SUM(Master_Rev[AMOUNT]) - Master_Rev[On hold],

    FILTER(Master_Rev, Master_Rev[REVENUE_CATEGORY] = "Recurring" ))

I want to add to the formula the following logic,

If the Master_Rev[Region] = 'USA' then do SUM(Master_Rev[AMOUNT_USD]) - Master_Rev[On hold] 
If the Master_Rev[Region] = 'JPY' then do SUM(Master_Rev[AMOUNT]) - Master_Rev[On hold]

essentially based on the Region, I want to use AMOUNT_USD or AMOUNT to do the sum. How can I accomplish this? Thanks in advance!

[ray_aramburo]

First, create the measures to use separately, for example:

USDAmount = CALCULATE(SUM(Master_Rev[AMOUNT_USD]) - SUM(Master_Rev[On hold]), Master_Rev[REVENUE_CATEGORY] = "Recurring")

StdAmount = CALCULATE(SUM(Master_Rev[AMOUNT]) - SUM(Master_Rev[On hold]), Master_Rev[REVENUE_CATEGORY] = "Recurring")

Then use a logic like: 

Amount Per Region = IF(VALUES([Master_Rev[Region]) = "USA", [USDAmount], IF(VALUES([Master_Rev[Region]) = "JPY", [StdAmount], [OtherValueOrMeasureYouPrefer]))

If it doesn't work with VALUES() try SELECTEDVALUE()

You can get creative and built it with field parameters as well but that would depend on users selecting/changing the measure. 

[jpbi23]

I would have to do this for various revenue categories this way. Is there a way to just do it in one formula?

[ray_aramburo · Accepted solution]

My recommendation is to separate each revenue formula and then you can use a SWITCH formula to simplify the logic. Yes it can be written in one formula but I wouldn't recommend it. 

For the Switch formula it would be: 

RevenueResult = SWITCH(Mater_Rev[Region], "USA", [USAMeasure], "JPY", [JPYMeasure], "Region 3", [Region 3 Measure])