Forum Discussion

rachaelwalker's avatar
rachaelwalker
Resolver III
2 years ago
Solved

Switch Statement to multiply different measures based on category name

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.

 

@Daxhelp
@help
  • rachaelwalker's avatar
    rachaelwalker
    2 years ago

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

2 Replies

  • rachaelwalker's avatar
    rachaelwalker
    Resolver III
    2 years ago

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

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