Forum Discussion

onkelphill's avatar
onkelphill
Frequent Visitor
8 years ago

Calculate Total Price | Lookup values

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

 

 

 

Need Help

6 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      onkelphill
      Frequent Visitor
      8 years ago

      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!

       

      • bsas's avatar
        bsas
        Icon for Post Patron rankPost Patron
        8 years ago

        onkelphill

         

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