Forum Discussion

gottapu's avatar
gottapu
Frequent Visitor
5 years ago
Solved

Employee FTE calculation

Hi,   I have data similar to tables below. Fact table that I se connects to number of other dimension tables and so number of rows per each assignment id are much larger but I have simplified it to...
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

    gottapu how about changing the code like this?

    Employee FTE:=IF(COUNTROWS('Fact'),SUMX(ALL(Assignment[Asgn ID]),CALCULATE(MAX('Fact'[Assignment FTE]),ALL(RepGroup[Rep Group Name]),ALL('Fact'[Assignment FTE]))))
gottapu's avatar
gottapu
Frequent Visitor
5 years ago

Thanks much wdx223_Daniel. Really appreciate you taking the time to replicate our scenario. Below solution has got me very close. We are displaying Assignment ID from dimension and when we do that Employee FTE and Assignment FTE are now showing same values (Employee FTE values not rolling up). But when we show Assignment ID from fact table, Employee FTE values are rolling up correctly. In reality, we have Assignment Number in our Assignments dimension that we show in our report. Appreciate, if you can let me know how best to adjust code when we are using Assignment ID from Assignment dimension in our report.

 

Thank you

-Raj

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

    gottapu how about adjust the code like this

     

    • gottapu's avatar
      gottapu
      Frequent Visitor
      5 years ago

      Thank you so much wdx223_Daniel . I have been working on this calc for last few days and breaking my head. I am relatively new to Power BI DAX code. I have gone through Plural sight DAX videos and other DAX documentation available on web but still struggled to get to correct number. You have made my day. Thank you so much for going extra mile to help.

       

      Thanks,

      -Raj

    • gottapu's avatar
      gottapu
      Frequent Visitor
      5 years ago

      Hi wdx223_Daniel,

       

      I have run into a slight problem where if I bring in additional column from another dimension, FTE values appears to be not rolling up. For example, I have Representation Group dimension that is connected to my fact on Rep group id field. For Example, Assignment IDs 21 has a representation group of 'Faculty' value and and Assignment ID 22 as a representation group of 'Clinician'. Though representation groups are at the assignment level, they are connected to Fact table using a different key (i.e Rep ID). Is there a way I can include Representation group in my report but still calculate FTE by Employee?

       

      Output that I am looking for:

      Employee NameAssignment IDRep GroupAssignment FTEEmployee FTE
      A11Faculty11
      B21Faculty0.50.7
      B22Clinician0.20.7
      C31Faculty0.30.7
      C32Clinician0.10.7
      C33MG Fcaulty0.30.7

       

       

      Rep Group Table:

      Rep Group IDRep Group Name
      1Faculty
      2MG Faculty
      3Clinician

       

       

      For Employee B and C, I am getting same values for Employee FTE and Assignment FTE. Apreciate any further help.

       

      Thanks,

      -Raj

       

       

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Icon for Community Champion rankCommunity Champion
        5 years ago

        gottapu how about changing the code like this?

        Employee FTE:=IF(COUNTROWS('Fact'),SUMX(ALL(Assignment[Asgn ID]),CALCULATE(MAX('Fact'[Assignment FTE]),ALL(RepGroup[Rep Group Name]),ALL('Fact'[Assignment FTE]))))