Forum Discussion
Multiple Sale Result by Second Table Values
Hi Experts
Here is the first question which gives me the correct answer for the first part.
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 |
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")
17 Replies
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
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]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.
Hi, Anonymous
I cannot find a link.
Can you share the link again, please?
