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 have two tables. Sales and Return tables that show sales and return amt for different products. I want to show (or chart) in a table, the total sales and return cost for each item. When I do that, the sales amt is shown correctly but the return amt is shown as one single figure. I don't have any relationship defined between these two tables.
How do I do this correctly?
Solved! Go to Solution.
Hi @Anonymous,
Create a summary table based on Product Return table.
Summary Product Return = SUMMARIZE ( 'Product Return', 'Product Return'[Product], "Return Total", SUM ( 'Product Return'[Return] ) )
In Product Sales table, add a new calculated column using LookUpValue.
return = LOOKUPVALUE ( 'Summary Product Return'[Return Total], 'Summary Product Return'[Product], 'Product Sales'[Product] )
Then, add columns [Product], [Sales] and [return] from 'Product Sales' into table visual. Please note that when you add [return] into table visual, you should choose 'Don't summarize ' option.
If you need to show total values for this column, that is to say, you want it to be summed. Please use this formula instead:
return2 = LOOKUPVALUE ( 'Summary Product Return'[Return Total], 'Summary Product Return'[Product], 'Product Sales'[Product] ) / CALCULATE ( COUNT ( 'Product Sales'[Product] ), ALLEXCEPT ( 'Product Sales', 'Product Sales'[Product] ) )
You can see the difference between above two formulas.
Best regards,
Yuliana Gu
Hi @Anonymous,
Create a summary table based on Product Return table.
Summary Product Return = SUMMARIZE ( 'Product Return', 'Product Return'[Product], "Return Total", SUM ( 'Product Return'[Return] ) )
In Product Sales table, add a new calculated column using LookUpValue.
return = LOOKUPVALUE ( 'Summary Product Return'[Return Total], 'Summary Product Return'[Product], 'Product Sales'[Product] )
Then, add columns [Product], [Sales] and [return] from 'Product Sales' into table visual. Please note that when you add [return] into table visual, you should choose 'Don't summarize ' option.
If you need to show total values for this column, that is to say, you want it to be summed. Please use this formula instead:
return2 = LOOKUPVALUE ( 'Summary Product Return'[Return Total], 'Summary Product Return'[Product], 'Product Sales'[Product] ) / CALCULATE ( COUNT ( 'Product Sales'[Product] ), ALLEXCEPT ( 'Product Sales', 'Product Sales'[Product] ) )
You can see the difference between above two formulas.
Best regards,
Yuliana Gu
Thanks Yuliana !!
Hi, check this
https://msdn.microsoft.com/en-us/library/gg492170.aspx and this https://msdn.microsoft.com/en-us/library/ee634551.aspx
Use Earlier function to lookup a value for any single row...
The output in your case should be
1. Add a new column in "Sales" table, you can give it a name "Return"
2. Insert this formula in "Sales" table", column "Return"
LOOKUPVALUE('Table in whis the return is'[Return],'Table in which the return is'[Product],earlier[Product])
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |