Forum Discussion

BWL's avatar
BWL
Helper II
8 years ago
Solved

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 - Dat...
Need Help
Show and Tell
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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]))

    DAX_Formula

     

    BTW, please be aware of your privacy.

     

    Best Regards,

    Dale

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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's avatar
    BWL
    Helper II
    8 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      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]))

      DAX_Formula

       

      BTW, please be aware of your privacy.

       

      Best Regards,

      Dale

      • BWL's avatar
        BWL
        Helper II
        8 years ago

        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