Forum Discussion

jpbi23's avatar
jpbi23
Icon for Helper I rankHelper 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" )) ...
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])
jpbi23's avatar
jpbi23
Icon for Helper I rankHelper 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
Icon for Super User rankSuper 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])