cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate IF using sum and filter.

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!
1 ACCEPTED SOLUTION
Super User

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])``

Proud to be a Super User!

3 REPLIES 3
Super User

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.

Proud to be a Super User!

Frequent Visitor

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

Super User

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])``

Proud to be a Super User!