Forum Discussion
Helper ICalculate IF using sum and filter.
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])
Super UserFirst, 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.
jpbi23I would have to do this for various revenue categories this way. Is there a way to just do it in one formula?
- ray_aramburo
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])