topic Switch Statement to multiply different measures based on category name in DAX Commands and Tips

Switch Statement to multiply different measures based on category name

rachaelwalker — Thu, 22 Feb 2024 18:06:26 GMT

I am trying to create a DAX measure to calculate the Revenue Forecast based on the age of opportunities. Each age category has a different conversion rate based on historical rates. I placed the DAX below in a calculated column and it comes through in a card visual but not on the table which is where I need it. I have tried both IF and SWITCH statements. I think I need it to be a measure but the measure is not seeing my Age Category column.

Revenue Forecast = SWITCH(
TRUE,
'Opportunity'[Age Category] = "0-90 Days", ([TotalRevenue] * [90DayRateWon] * [Conversion Rate R12M]),
'Opportunity'[Age Category] = "90-180 Days", ([TotalRevenue] * [180DayRateWon] * [Conversion Rate R12M]),
'Opportunity'[Age Category] = "180-270 Days", ([TotalRevenue] * [270DayRateWon] * [Conversion Rate R12M]),
0)

For the first category 0-90 Days, I want it to take $260,573.54 X [90DayRateWon] X [Conversion Rate R12M] etc...Thank you for taking the time.

Re: Switch Statement to multiply different measures based on category name

rachaelwalker — Thu, 22 Feb 2024 19:24:52 GMT

I finally got it working using a measure with variables.

Revenue Forecast Measure =

VAR AgeCategory = SELECTEDVALUE('Opportunity'[Age Category])

VAR Conversion = [Conversion Rate R12M]

RETURN

IF(

ISBLANK(AgeCategory),

BLANK(),

SWITCH(

TRUE(),

AgeCategory = "0-90 Days", [TotalRevenue] * [90DayRateWon] * Conversion,

AgeCategory = "90-180 Days", [TotalRevenue] * [180DayRateWon] * Conversion,

AgeCategory = "180-270 Days", [TotalRevenue] * [270DayRateWon] * Conversion,

AgeCategory = "270+ Days", [TotalRevenue] * [270+DayRateWon] * Conversion, 0

)

Re: Switch Statement to multiply different measures based on category name

Fowmy — Thu, 22 Feb 2024 19:27:48 GMT

rachaelwalker

Please try:

Revenue Forecast Measure = SUMX ( 'Opportunity', SWITCH ( TRUE (), 'Opportunity'[Age Category] = "0-90 Days", [TotalRevenue] * [90DayRateWon] * [Conversion Rate R12M], 'Opportunity'[Age Category] = "90-180 Days", [TotalRevenue] * [180DayRateWon] * [Conversion Rate R12M], 'Opportunity'[Age Category] = "180-270 Days", [TotalRevenue] * [270DayRateWon] * [Conversion Rate R12M], 0 ) )