Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
I finally got it working using a measure with variables.
@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
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I finally got it working using a measure with variables.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
23 | |
15 | |
11 |
User | Count |
---|---|
76 | |
58 | |
46 | |
17 | |
12 |