Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
david_flu
Frequent Visitor

how can i write the Lookup functions please

Hello

 

Could i please get some help with the below tables please. I'm trying to add a column which shows the total sale amount for each product / each region / each week. is there a way to do it ? snip below for the product sales / product details table.

 

david_flu_0-1697862935250.png

 

Many thanks in advance!

 

 

2 ACCEPTED SOLUTIONS
danextian
Super User
Super User

Hi @david_flu 

You did not mention how to calcualte the sales amount but I am assuming it is unit price * kg. If so, try this calc column in your first table:

Sales =
VAR __UNIT_PRICE =
    LOOKUPVALUE (
        'table2'[Unit Price],
        'table2'[Regions], 'table1'[Store],
        'table2'[Product], 'table1'[Regions]
    )
RETURN
    __UNIT_PRICE * 'Table1'[KGs]

If this isn't what you're looking for, please elaborate your use case and provide a sample data we can easily copy-paste (not an image) as well as your sample result.  Please refer to this sticky post when creating new posts in the future: https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/3332... 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

parry2k
Super User
Super User

@Daniel_PowerBI the solution @danextian should work and you can also do this:

 

add 2 new columns in each table and then set the relationship on these new columns, which will be one to many, one will be on the store/product side table. 

 

//add new column in Sales Table
Surrogate Key = SalesTable[Store] & SalesTable[Product]

//add new column in Store Product table
Surrogate Key = StoreProductTable[Store] & StoreProductTable[Product]

//add new measure for sales
Sales Measure = 
SUMX (  SalesTable, SalesTable[Product Sales] & RELATED ( StoreProductTable[Unit Price] ) )

 

and to visualize. use Week/Day/Store column from SalesTable and the measure Sales Measure

 

Change the column names and table names as per your data model.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

12 REPLIES 12
parry2k
Super User
Super User

@Daniel_PowerBI @david_flu I'm done with this post, everything is confusing - are you looking for a solution or going to give the suggestions without following the solution? Thank you!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k , I have followed your steps and still struggling with it. as i have mentioned before that i'm very new to this..

The steps i have done was good until the last measure, somehow it can't find the column for the unit price. please advise where i have done wrong.

 

i have created 2 unique tables for the relationship, 1) StoreTable. 2) ProductTable.

 

step 1 below is where i created new column from the SalesTable.

david_flu_0-1697943606811.png

 

setp 2 below is new culomn created from the StoreProductTable

david_flu_1-1697943695886.png

 

step 3 below is in error as i don't know where i done wrong and it's located in the SalesTable

david_flu_2-1697943900248.png

 

all tables below

david_flu_3-1697944029570.png

 

below is the relationships page

david_flu_4-1697944087422.png

 

 

Thanks

parry2k
Super User
Super User

@david_flu I don't think this will work or is the right approach, I told you to add calculated columns first to connect which I think you completely missed.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Not sure what's going on here @parry2k 

 

@david_flu I don't think this will work or is the right approach, I told you to add calculated columns first to connect which I think you completely missed.

 

david_flu why do you want to add a calculated column? For sales and total sales, you can easily use measures which is the best way to go. What is the rational behind asking for a calculated column?

parry2k
Super User
Super User

@Daniel_PowerBI the solution @danextian should work and you can also do this:

 

add 2 new columns in each table and then set the relationship on these new columns, which will be one to many, one will be on the store/product side table. 

 

//add new column in Sales Table
Surrogate Key = SalesTable[Store] & SalesTable[Product]

//add new column in Store Product table
Surrogate Key = StoreProductTable[Store] & StoreProductTable[Product]

//add new measure for sales
Sales Measure = 
SUMX (  SalesTable, SalesTable[Product Sales] & RELATED ( StoreProductTable[Unit Price] ) )

 

and to visualize. use Week/Day/Store column from SalesTable and the measure Sales Measure

 

Change the column names and table names as per your data model.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

hi @parry2k . i'm very very new to this and have a very little knowledge. thought that i need to add a column to write the lookup function in. will try your solution first and get back to you.

 

many thansk

parry2k
Super User
Super User

@david_flu why do you want to add a calculated column? For sales and total sales, you can easily use measures which is the best way to go. What is the rational behind asking for a calculated column?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

david_flu
Frequent Visitor

Hi @danextian 

 

Thanks for the help and apologies that i didn't explain clearly. please view tables below and i would like to use to add column function to have the daily summary of each product sales for each store  each week.

 

WEEK       DAY        STORE      PRODUCT SALES AMOUNT TOTAL SALES

week 1MONDAYEASTAPPLE120 
week 1MONDAYEASTORANG129 
week 1MONDAYEASTBANANA137 
week 1MONDAYWESTAPPLE224 
week 1MONDAYWESTORANG238 
week 1MONDAYWESTBANANA208 
week 1MONDAYNORTHAPPLE102 
week 1MONDAYNORTHORANG87 
week 1MONDAYNORTHBANANA89 
week 1MONDAYSOUTHAPPLE172 
week 1MONDAYSOUTHORANG170 
week 1MONDAYSOUTHBANANA190 
week 1MONDAYCENTRALAPPLE247 
week 2MONDAYCENTRALORANG221 
week 2MONDAYCENTRALBANANA232 

 

STORE      PRODUCT   UNIT PRICE

EASTAPPLE$3.30
EASTORANG$4.20
EASTBANANA$3.50
WESTAPPLE$2.20
WESTORANG$3.80
WESTBANANA$2.90
NORTHAPPLE$1.90
NORTHORANG$2.30
NORTHBANANA$1.80
SOUTHAPPLE$3.30
SOUTHORANG$5.20
SOUTHBANANA$3.90
CENTRALAPPLE$5.30
CENTRALORANG$5.80
CENTRALBANANA$5.20

 

 

Many thanks

Something like this?

Daniel_PowerBI_1-1697925545637.png

 

 

 

@Daniel_PowerBI , yes please. this what i'm trying to do. Thanks

Perfect,

Connect your Tables like this

Daniel_PowerBI_0-1697930841474.png

Visual like this:

Daniel_PowerBI_1-1697930921240.png

 and here the mesure for the $:

$ = SUM('Price'[Price])* SUM(Sales[Sales aMount])
 
Let me know if it works
danextian
Super User
Super User

Hi @david_flu 

You did not mention how to calcualte the sales amount but I am assuming it is unit price * kg. If so, try this calc column in your first table:

Sales =
VAR __UNIT_PRICE =
    LOOKUPVALUE (
        'table2'[Unit Price],
        'table2'[Regions], 'table1'[Store],
        'table2'[Product], 'table1'[Regions]
    )
RETURN
    __UNIT_PRICE * 'Table1'[KGs]

If this isn't what you're looking for, please elaborate your use case and provide a sample data we can easily copy-paste (not an image) as well as your sample result.  Please refer to this sticky post when creating new posts in the future: https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/3332... 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.