Forum Discussion

jpbi23's avatar
jpbi23
Helper I
2 years ago
Solved

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!
Need Help
Tips and Tricks
  • ray_aramburo's avatar
    ray_aramburo
    2 years ago

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

3 Replies

  • ray_aramburo's avatar
    ray_aramburo
    Super User
    2 years ago

    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's avatar
      jpbi23
      Helper I
      2 years ago

      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's avatar
        ray_aramburo
        Super User
        2 years ago

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