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]))))
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

gottapu , Try a measure like

 

Employee FTE =calculate(sum(Table[Assignment FTE]), filter(allselected(Table),[Employee Name] = max([Employee Name])))

 

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

  • gottapu's avatar
    gottapu
    Frequent Visitor
    5 years ago

    Thanks Amit for your quick reply. Unfortunately, after trying above, I am getting Employee FTE as sum of all Assignment FTE values in the fact table and hence they are way more than what I am trying to get. For Employee ID =1, I am getting Employee FTE as value 3.

     

    Thanks

    -Raj

     

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

      gottapu i think this code can help you

       

      • 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