Search code examples
exceldaxpowerpivot

Alternative to LOOKUPVALUE function for use within a measure?

I have a measure that successfully calculates a cumulative amount of planned revenue through each month:

Revised Revenue :=
CALCULATE (
    SUM ( Rev_Revised[Revenue] ),
    Data_Types[Data Types] = "Projected"
)

Cumulative Revised Revenue :=
IF (
    MIN ( MonthDate[MonthDate] )
        <= CALCULATE ( MAX ( Rev_Revised[MonthDate] ), ALL ( Rev_Revised ) ),
    CALCULATE (
        [Revised Revenue],
        FILTER (
            ALL ( MonthDate[MonthDate] ),
            MonthDate[MonthDate] <= MAX ( MonthDate[MonthDate] )
        )
    )
)

When used in a pivot table in conjunction with Running Total In..., it looks like this:

image 1

What I need to do now is create a new measure that multiplies a Revenue Capture % to each monthly cumulative amount (this is like a prediction on the lag of actually collecting the revenue), and those percentages are found in a related table:

image 2

Using LOOKUPVALUE in a measure obviously throws an error because it doesn't result in one value. I could easily just create my own additional row under the pivot table that performs this calculation, but I'd really like it to be performed within the data model. Any ideas?

Edit: The closest solution I can come to now is a calculated column in my spend plan dataset that looks up the % for each row:

Monthly Rev Capture :=
LOOKUPVALUE (
    Rev_Capture[Revenue Capture],
    Rev_Capture[MonthDate], Rev_Revised[MonthDate]
)

And then a measure of this:

Rev Capture Amount :=
[Cumulative Revised Revenue] * AVERAGE ( Rev_Revised[Monthly Rev Capture] )

But that doesn't calculate correctly in the pivot table. Here's what it looks like, also with the correct manual calculations displayed below the table for validation:

enter image description here

What I need is a measure similar to one of these, but of course these fail:

Rev Capture Total:=
[Cumulative Revised Revenue]*RELATED(Rev_Capture[Revenue Capture])

Rev Capture Total v2:=
     [Cumulative Revised Revenue]*LOOKUPVALUE(Rev_Capture[Revenue Capture],
MonthDate[MonthDate],
Rev_Capture[MonthDate])
Solution

  • Your v2 measure looks close but since it's not a calculated column, you can't just reference a column without using an aggregating function like AVERAGE or MAX or VALUES since the lookup function requires a single value to look up.

    Try it like this:

    Rev Capture Total v2 :=
[Cumulative Revised Revenue]
    * LOOKUPVALUE (
        Rev_Capture[Monthly Rev Capture],
        Rev_Capture[MonthDate], SELECTEDVALUE ( MonthDate[MonthDate] )
    )

    If your filter context has a single month, SELECTEDVALUE will return the same as other aggregators like SUM/MAX/AVERAGE/VALUES but will return a blank if there are multiple values in the filter context since there's not just one option.