Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
@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.
Solved! Go to 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.
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.
Hi @Rubal,
I have adjusted the data model (see figure) and used the Rest. Number and SubCategory field from the bridge tables:
Regards FrankAT
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?
@Rubal , Not very clear to me. You can create common dimensions rest. Number and subcategories and join with your table and analyze?
@Rubal , allocation of total buy.
Can you share sample data and sample output in table format?
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |