Multiple Sale Result by Second Table Values

[Anonymous]

Hi  Experts

Here is the first question which gives me the correct answer for the first part. 

link: https://community.powerbi.com/t5/Desktop/Calculate-Sales-by-Channel-by-Region-by-County-by-Month/m-p...

The releationship between FACT Sales Table and Direct Cost Table (see below) is via Channel. Once the forst step has been computated i want to use cost type OCGS and find the corresponding month (if its Jan 20 in Sales table) then Jan 20 in Direct cost table and mulitple the result by the corresponding value from the Direct Cost Table. 

i would like to add this step to the calculated Column in the first question. 

Channel	Cost Type	Month Year	Value
OOO	OCOGS	Jan-20	35
OOO	OCOGS	Feb-20	32
OOO	OCOGS	Mar-20	33
OOO	OCOGS	Apr-20	27
OOO	OCOGS	May-20	10
OOO	OCOGS	Jun-20	11
OOO	OCOGS	Jul-20	9
OOO	OCOGS	Aug-20	55
OOO	OCOGS	Sep-20	62
OOO	OCOGS	Oct-20	39
OOO	OCOGS	Nov-20	28
OOO	OCOGS	Dec-20	30

[Jihwan_Kim]

Hi, @Anonymous 

Please try to use the below for the calculated column. I combined it with the previous one.

Please kindly let me know if it works or not.

newcolunm =
DIVIDE (
Facts[Sales],
CALCULATE (
SUMX ( Facts, Facts[Sales] ),
ALL ( Facts ),
VALUES ( Facts[Channel] ),
VALUES ( Facts[Region] ),
VALUES ( Facts[Year] ),
VALUES ( Facts[Country] )
)
)
*

LOOKUPVALUE (
Costs[Value],
Costs[Month], Facts[Month],
Costs[Year], Facts[Year],
Costs[Channel], Facts[Channel]
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Hi Kim Can we amend the following so Cost Type in Cost Table = "4.Purch" where i could specific the selection criteria

[Jihwan_Kim]

Hi, @Anonymous 

I cannot find a link.

Can you share the link again, please?

[Anonymous]

link: https://www.dropbox.com/s/wlpgrtig2nuynvl/Test.pbix?dl=0 

In my master file Append_Direct Ohds_IAM&OES - i have month year split out...

Kindly just post the DAX not a new file, Please.

ref to Sale_Data_Master for the Calculated column Error

[Jihwan_Kim]

Hi, @Anonymous 

Without the source data connected, I cannot use Power Query to split MonthYear column and have Month-Full-Name in order to connect with Sale_Data_Master's Month Column.

[Jihwan_Kim]

Hi, @Anonymous 

I think you deleted the error column in your Sales Data Master Table.

May I ask,

- Lookupvalue from "Which Table", and bring it to "Where" ??

- In AppendDirect Ohds table, the month name is Jan, Feb, Mar, ... but your Sales Data master Table has January, February, March, ... -> Can I create one more column to match the length of the name?

[Anonymous]

Hi Kim yes...you can and I want to lookup value in  AppendDirect Ohds table..just tell me what to do and I'll follow your steps. Don't post another pbix.

Where to sale data master.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I am not sure if the below works.

LookupCost = LOOKUPVALUE( 'Append_Direct Ohds_IAM&OES'[Value], 'Append_Direct Ohds_IAM&OES'[Month], Sales_Data_Master[MonthV2], 'Append_Direct Ohds_IAM&OES'[Year], Sales_Data_Master[Years_In_Date], 'Append_Direct Ohds_IAM&OES'[Channel], Sales_Data_Master[Channel], 'Append_Direct Ohds_IAM&OES'[Country], Sales_Data_Master[Sales_Country], 'Append_Direct Ohds_IAM&OES'[Type of Cost], "4.Purch")

[Anonymous]

thank you sir

[Anonymous]

What results did you get?

[Jihwan_Kim]

I got all 0.

I don't think it is correct.

😓

[Anonymous]

sample Data

https://www.dropbox.com/s/727dslw29huny80/Test.pbix?dl=0 

[Anonymous]

Hold on ill Split the column and upload a new file....10 minutes

[Anonymous]

Hi Kim - ill upload a link to sample file.

[ryan_mayu]

@Anonymous 

What does your direct cost table look like?

I guess you can try

column=maxx(filter(direct cost table, salestable[channel]=directcosttable[channel]&&salestable[type]=directcosttable[type]&&salestable[month]=directcosttable[month]),directcosttable[value]) * salestable[value]

[AllisonKennedy]

@Anonymous  Why are you needing this as a calculated column? If you create it as a measure it will use the context of the table you put it in, and when you slice by Month-Year from your DimDate table (assuming you have one???) then it will automatically pull the correct Value from both tables. 

Can you share a screenshot of your relationships please?

[Anonymous]

Hi Allison - firstly thanks for looking at my question. I'll share a file later on today. Need to strip out sensitive data.