how can i write the Lookup functions please

[david_flu]

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.

Many thanks in advance!

[danextian · Accepted solution]

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... 

[parry2k]

@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!

[david_flu]

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.

setp 2 below is new culomn created from the StoreProductTable

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

all tables below

below is the relationships page

Thanks

[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.

[Daniel_PowerBI]

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 · Accepted solution]

@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.

[david_flu]

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]

@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?

[david_flu]

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 1	MONDAY	EAST	APPLE	120
week 1	MONDAY	EAST	ORANG	129
week 1	MONDAY	EAST	BANANA	137
week 1	MONDAY	WEST	APPLE	224
week 1	MONDAY	WEST	ORANG	238
week 1	MONDAY	WEST	BANANA	208
week 1	MONDAY	NORTH	APPLE	102
week 1	MONDAY	NORTH	ORANG	87
week 1	MONDAY	NORTH	BANANA	89
week 1	MONDAY	SOUTH	APPLE	172
week 1	MONDAY	SOUTH	ORANG	170
week 1	MONDAY	SOUTH	BANANA	190
week 1	MONDAY	CENTRAL	APPLE	247
week 2	MONDAY	CENTRAL	ORANG	221
week 2	MONDAY	CENTRAL	BANANA	232

STORE      PRODUCT   UNIT PRICE

EAST	APPLE	$3.30
EAST	ORANG	$4.20
EAST	BANANA	$3.50
WEST	APPLE	$2.20
WEST	ORANG	$3.80
WEST	BANANA	$2.90
NORTH	APPLE	$1.90
NORTH	ORANG	$2.30
NORTH	BANANA	$1.80
SOUTH	APPLE	$3.30
SOUTH	ORANG	$5.20
SOUTH	BANANA	$3.90
CENTRAL	APPLE	$5.30
CENTRAL	ORANG	$5.80
CENTRAL	BANANA	$5.20

Many thanks

[Daniel_PowerBI]

Something like this?

[david_flu]

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

[Daniel_PowerBI]

Perfect,

Connect your Tables like this

Visual like this:

 and here the mesure for the $:

$ = SUM('Price'[Price])* SUM(Sales[Sales aMount])

Let me know if it works

[danextian · Accepted solution]

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...