Calculate Total Price | Lookup values

[onkelphill]

Hello everyone,

looking for help to solve with the following, probably standard, scenario.

I need to calculate the total price ( quantity * rate) for each transaction of the Lanes table. Ultimately i need  to compare prices to find the lowest bid. 

So, i was trying to define a measure to do the following:

For each supplier multiply quantity * rate giving the following conditions:

Lanes[Lane ID] = Quotations[LaneID]

Lanes[Bracket] = Quotations[Bracket]

I solved this problem in the past creating calculated columns to calculate prices for each and every supplier individually. 

The data model can be simplified as shown below:

Two tables:

Lanes (fact table)

Quotations

[v-chuncz-msft]

@onkelphill,

You may consider using RANKX Function to add a calculated column first. 

Column =
RANKX (
    CALCULATETABLE (
        Quotations,
        ALLEXCEPT ( Quotations, Quotations[LaneID], Quotations[Bracket] )
    ),
    Quotations[Rate],
    ,
    ASC,
    DENSE
)

[onkelphill]

@v-chuncz-msft 

thank you very much.

Actually, i found this example and it is exactly what i am looking for. 

However, although i am following the instructions it does not work for me. This is the error message i am getting:

"A table of multiple values was supplied where a single value was expected"

Any ideas?

Thank you very much!

[v-chuncz-msft]

@onkelphill,

Take a good look at LOOKUPVALUE Function.

[bsas]

@onkelphill

This message says that you're using calculated column but should use measure instead.

[onkelphill]

@bsas

i know. Funny thing is, looking at the example i linked above it works.

[Ashish_Mathur]

Hi @onkelphill,

Have you resolved this problem?  If not, share the link from where i can download you file and also show your expected result there.