Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]
Solved! Go to 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])
Proud to be a 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!
I would have to do this for various revenue categories this way. Is there a way to just do it in one formula?
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!