Calculated Column - Look up Value

[Rubal]

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.

[v-juanli-msft · Accepted solution]

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.

[FrankAT]

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]

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?

[amitchandak]

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

[Rubal]

Hi @amitchandak ,

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

[amitchandak]

@Rubal , allocation of total buy.

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

[Greg_Deckler]

@Rubal - Should be a solvable problem. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.