Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
5 years ago
Solved

DAX calculation from a table

I am trying to do below calculation     

 

Sum of Terms / Average Headcount as example using 1/1/20+2/1/20 ...

 

230+231 = 461 (Sum of Terms)

3651+3696 =7,347 / 2 =3,673.5 (average headcount)

461/3,673.5 = 12.55%    but measure shows 12.71% need help with DAX

 

see screen shots below

Need Help

11 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    jcastr02 ,

    Hope you are doing like

    divide(sum(Table[Terms]), Average(Table[Headcount]))

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • jcastr02's avatar
      jcastr02
      Post Prodigy
      5 years ago

      @amitchandak Hello, please see screen shot of query above, the count type is in a different column ... I also provided an example in the screen shot in lower left side...

      • jcastr02's avatar
        jcastr02
        Post Prodigy
        5 years ago

        @Greg_Deckler Hello Greg, sorry about that, does this help?

        Business Unit File_date1 Count Count Type

        Miami1/1/2020581Headcount
        Chandler1/1/2020704Headcount
        Orlando Care1/1/2020699Headcount
        Orlando Support1/1/20201081Headcount
        Muscle Shoals1/1/2020586Headcount
        Muscle Shoals2/1/2020554Headcount
        Orlando Support2/1/20201100Headcount
        Chandler2/1/2020729Headcount
        Miami2/1/2020523Headcount
        Orlando Care2/1/2020790Headcount
        Orlando Support1/1/202017Terms
        Miami1/1/202036Terms
        Chandler2/1/202031Terms
        Orlando Support2/1/202025Terms
        Chandler1/1/202064Terms
        Orlando Care2/1/2020109Terms
        Miami2/1/202042Terms
        Muscle Shoals1/1/202019Terms
        Orlando Care1/1/202095Terms
        Muscle Shoals2/1/202023Terms
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    jcastr02 Sorry, having trouble following, can you post sample data as text and expected output?


    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, 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

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • jcastr02's avatar
      jcastr02
      Post Prodigy
      5 years ago

      Hello @Greg_Deckler I've provided an example above in screen shot using 1/1/20 +2/1/20

      I should have % at 12.55% - Can you take a look at my measure below to see what is incorrect.... trying to do sum of terms / average headcount

      Attrition new new.png

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        5 years ago

        @jcastr02 - Sorry, but I am not hand typing all of that in order to mock up a model. Maybe someone else will.