Forum Discussion

Rubal's avatar
Rubal
Helper II
6 years ago
Solved

Calculated Column - Look up Value

Hi,

 

I need some help with a calculated column. I have a "Buy" table and a "Sell" table. I want to group all the subcategories from the "Buy" table and show it against the "Sell" table as an output (the difference between buy and sell).

The usual lookupvalue formula doesn't work and I get this error "A table of multiple values was supplied where a single value was expected" likely so for my data structure as there are duplicates.

Calculated column 1 = 

LOOKUPVALUE(Sell[Total Sell],Sell[Rest. Number], Buy[Rest. Number])

 

I changed the formula with below it is somewhat working but showing the wrong total sells value. Any help will be appreciated. 

 

Calculated Column =
CALCULATE(
FIRSTNONBLANK(Sell[Total Sell],1),
FILTER(ALLSELECTED(Sell),Sell[Rest. Number]= Buy[Rest, Number]
&& Sell[SubCategory] = Buy[SubCategory]))

 

 

 

OutputSell TableBuy Table

 

Zubair_Muhammad - I know you have provided some great help on this before. Appreciate if you please can identify what I am doing wrong, to sum up the total sell next to total buy to work out the difference.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Rubal 

    Create a new table

     

    link table =
DISTINCT (
    UNION (
        SUMMARIZE (
            sell,
            sell[Rest.Number],
            sell[Sub Category],
            sell[total sell],
            "cate", "totalsell"
        ),
        SUMMARIZE (
            buy,
            buy[Rest.Number],
            buy[Sub Category],
            buy[total buy],
            "cate", "totalbuy"
        )
    )
)

     

    Rename [total sell] as [value].

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Rubal 

    Create a new table

     

    link table =
DISTINCT (
    UNION (
        SUMMARIZE (
            sell,
            sell[Rest.Number],
            sell[Sub Category],
            sell[total sell],
            "cate", "totalsell"
        ),
        SUMMARIZE (
            buy,
            buy[Rest.Number],
            buy[Sub Category],
            buy[total buy],
            "cate", "totalbuy"
        )
    )
)

     

    Rename [total sell] as [value].

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Rubal , Not very clear to me. You can create common dimensions rest. Number and subcategories and join with your table and analyze?

    • Rubal's avatar
      Rubal
      Helper II
      6 years ago

      Hi amitchandak ,

       

      which part is not clear to you?? and No, creating a common dimension didn't work.

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Rubal , allocation of total buy.

         

        Can you share sample data and sample output in table format?

  • FrankAT's avatar
    FrankAT
    Community Champion
    6 years ago

    Hi Rubal,

    I have adjusted the data model (see figure) and used the Rest. Number and SubCategory field from the bridge tables:

     

     

    Regards FrankAT

    • Rubal's avatar
      Rubal
      Helper II
      6 years ago

      Hi FrankAT . Thanks for your advise. I have tried doing it. Howver, as I have some other table which have existing relationship with both buy and sell table, I couldn't activate the relationship with the bridge as you have shown. any chance of helping me with the formula?