Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to Solution.
Hi, @Anonymous
I am not sure if the below works.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Kim Can we amend the following so Cost Type in Cost Table = "4.Purch" where i could specific the selection criteria
Hi, @Anonymous
I cannot find a link.
Can you share the link again, please?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
Hi, @Anonymous
I am not sure if the below works.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
thank you sir
What results did you get?
I got all 0.
I don't think it is correct.
😓
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hold on ill Split the column and upload a new file....10 minutes
Hi Kim - ill upload a link to sample file.
@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]
Proud to be a Super User!
@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?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Allison - firstly thanks for looking at my question. I'll share a file later on today. Need to strip out sensitive data.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
107 | |
106 | |
81 | |
37 | |
35 |
User | Count |
---|---|
167 | |
107 | |
72 | |
66 | |
55 |