Forum Discussion
DAX Formula
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
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
4 Replies
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.
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
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
