Data modeling lookup table and Filtering lookup table using fact table

[Malal]

Hi everyone,
I am new to this forum and would appreciate your help, please ask me more questions if I miss any details. My goal is to summarize number of actual employees, projected employees and the delta by division and by position (finance, HR, developer,…),

current resources table contains: division, position, employee name,……

opportunities analysis table contains: Division, start date of the projects, estimate revenue, probability, duration in month, project type,….

Department profile table : is a matrix with division in in rows and position in columns. The values are % position needed by division. We add to the table a column revenue. I was expecting to unpivot all except division and revenue column

Currency table has currency and factor
data model.docx (119.6 KB)

1. Please can you help how to organize the data model. Below what I try.
2. This second question is this how to organize the data model, I would like to develop it in another to separate question, since I believe it’s about dax formula using lookupvalue,….the goal is to forecast the expected employees by division for the next six month based on the selected value…

My formula:

= DIVIDE(SUMX('Opportunities Analysis','Opportunities Analysis'[Est. Revenue]*'Opportunities Analysis'[Probability]*RELATED('Currency Conversion'[Factor]))/100,SUMX('Department Profile','Department Profile'[EmpRevenue]),0)

To the formula above in need to add value from the department profile table
And
Add the condition below

AND(
      ([@Department]="Air"),
      (YEAR(NOW())-YEAR([Est. Start Date]))*12+MONTH(NOW())+n-MONTH([Est. Start Date]) < [Est. Duration (mon)],
      (YEAR(NOW())-YEAR([Est. Start Date]))*12+MONTH(NOW())+n-MONTH([Est. Start Date]) >=0 )
n : n=0 four 1st month,n=1 for second month,……

[Malal]

Hi Sam, Hi everyone.
I need your help and willing to provide more details if needed to organize my data modeling.

Thanks

[sam.mckay]

Sorry for the delay here.

Really if you need some help on the data model you’ll probably have to add the model so an example can be drawn up. It’s hard to offer good advice without see what’s actually in the tables.

Here’s some quick ideas for you.

Fact Table = Opportunities Analysis

Lookup Tables = All the rest.

To understand how to work with these table I have to advise going through this section of this course here. This is essential!

Data Transformations & Modeling

This covers everything you need and know about how to model your data. If you watch these videos you’ll have your model sorted in 5 mins. I’m confident in that.

Until your model it sorted there’s no point in looking at DAX formulas. So start there first.

[Malal]

Hi Sam,
Thank you for your advice, please below more details. My data model is working, specially because of the relationship issue between the department profile and the opportunities analysis table. I am keeping the dax formulas for later. Thanks everyone for your help.

Department Profile

Department EmpRevenue

Current Resources

Currency Conversion

Opportunities Analysis

first try model: ?

[Malal]

Hi Sam,
Thanks for your comments, below the data model. Thank you

Headcount project test.xlsx (128.5 KB)
project.pbix (201.5 KB)

[sam.mckay]

I done a good evaluation of this and spent some time working out a model that I think works

This is where it’s currently landed.

Sometimes its hard to get my mind around every aspect of the data because scenarios can very a lot. But after looking through this for sometime I think this is the best set up I can think of.

Attached here
project.pbix (210.4 KB)

Look like you’re calculating a lot of what you need?

Make sure to use the columns from your lookup tables when showcasing results.

It looks like you mostly are already which is great. Just a reminder.

Chrs

[Malal]

Thank you Sam, I lost my Job 3 weeks ago, I did not check the forum, but my former employer still count on me to finsh this project, I will have one question that I will post on the dax calculation topic, but let me tell you this, now I have found a new one for 3 months , who now it’s could go longer, today I chat with my manager, he told me since the have dynamics Nav to count on me to use power bi dashboards, lie pnl, fsct,…Now I know why they hire me, it’s because I know power bi, the little I know is because of you, so thanks for helping me get a new job that will help me support my kids, my thanks to you.

[sam.mckay]

Ok sure, good luck with the new role.