Total noob so bear with me
I have a union table, Volume_Weeks, structured like this.
Inside the type column, I have three categories actual, forecast, and budget.
I wish to create a measure that combines YTD actuals with YTG forecast. I understand this would be very easy if my data was not in a flat union table (i.e. separate columns for actuals + forecast) because I could use a simple calculated column. However, I'm relucant to to adjust the data model so I would like to accomplish this with a measure.
I'm trying the following but I receive an error:
LE_FY = var actualVol = CALCULATE(SUM(Volume_Weeks[value]),Volume_Weeks[type]="actual") var LEVol = CALCULATE(SUM(Volume_Weeks[value]),Volume_Weeks[type]="LE") var LE_FY_Vol = IF(Volume_Weeks[Week_Num] < WEEKNUM(TODAY()), actualVol , LEVol) Return LE_FY_Vol
A single value for column 'Week_Num' in table 'Volume_Weeks' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum [...]
I don't know enough about measures yet to understand how/if this can be resolved. Assistance is appreciated!
Solved! Go to Solution.
You solved it! Thank you. If you have some time, can you explain how this formula works? the MAX function is confusing to me because the MAX value in [Week_Num] is 52 (of course). So is the MAX function evaluating the MAX(Week_Num) for each row-combination in the table?
MAX(Week_Num) evaluated for filter context, not for each row. When you start to use your measure in report, generate filter context and MAX(Week_Num) evaluating on currently visible rows of table.