DAX Formula

[BWL]

Dear all,

At the moment I'm struggling with a complex DAX calculation. 
I have 2 facttables and 2 dimension tables.

tbl_Route
- Date
- Platenumber

- Routecode
- Turnover

tbl_Cost
- Date
- Platenumber
- Cost

dim_Date
- Date
- Period

dim_Trucks
- Platenumber

What I want to do is to sum to create a CALCULATED COLUMN in tbl_Route which contains the cost equally divided by route in period. So for example:  Platenumber 01 had $1000 cost in period 1 and did 150 routes.  1000 / 150 =  6,67 cost per route.

I really don't know how to get it work that the formule filters for the right period.

Can somebody help me and explain me how I can get it done?

Thank's in advance.

Kind regards,

BWL

[Greg_Deckler]

Would be easier with example/sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, perhaps something along the lines of:

Column = 
VAR __Cost = CALCULATE(SUM(tbl_Cost[Cost]))
VAR __Routes = COUNTROWS(FILTER(ALL(tbl_Route),[Platenumber] = EARLIER([Platenumber])))
RETURN DIVIDE(__Cost,__Routes,0)

That doesn't factor in Period but too much guesswork without data. 

[BWL]

Hi Greg,

Thanks for your reply.
I read your blog and I will keep your recommendations in mind.

Here you find the sample pbix file :

Hope this will clearify my question.

Kind regards,

BWL

[v-jiascu-msft · Accepted solution]

Hi @BWL,

Actually, a measure would be easy and reasonable. The table tbl_Route doesn't have periods.

Measure = DIVIDE(sum(tbl_Cost[Cost]), COUNT(tbl_Route[Routecode]))

BTW, please be aware of your privacy.

Best Regards,

Dale

[BWL]

Hi @v-jiascu-msft,

Thank you for your reply and privacy concerns!
The whole data set is made up or had it something to do with the dropbox link?

It seems that this simple DIVIDE measurement does the job.

Thank you.

Kind regards,

BWL