Forum Discussion

ChirSidh's avatar
ChirSidh
Helper II
2 years ago

Do not aggregate Project level from the Work order level

in power bi matrix table, i have employee, project number in rows and in columns monthwise multiplier. When i drill down the project number, i have all the work orders present in the project number. I have the multiplier at work order level. I have calculated project level multiplier separately. However when i drill up, for the project it is automatically showing the aggregate of work order level multiplier, i dont want to aggregate this , i want to show for a project level a separate calculated multiplier

Need Help

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    2 years ago

    Hey ChirSidh 

     

    you can IF and ISINSCOPE (https://dax.guide/isinscope/) to check the "level" you are currently looking at, and determine the "calculated multiplier" you want to display.

     

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom 

    • ChirSidh's avatar
      ChirSidh
      Helper II
      2 years ago

      Hi Tom, i am using this formula , 

      Show Bottom Hierarchy Values =
          IF(
              ISINSCOPE('Multiplier Movement'[Work Order No]) || ISINSCOPE('Multiplier Movement'[Work Order Description]),
              'Multiplier Movement'[Individual Multiplier],  -- Display individual multiplier for the bottom hierarchy
              IF(ISINSCOPE('Multiplier Movement'[ Project]), 'ProjectLevel Mult'[Project Level Multiplier], BLANK())  -- Display project multiplier for the project level
          )
       
      However this measure, 'ProjectLevel Mult'[Project Level Multiplier] - Project Level Multiplier =
      DIVIDE(
      SUM('ProjectLevel Mult'[ProjectNetRevenue]),
      SUM('ProjectLevel Mult'[ProjectDirectLabourCost]),
      0
      )
       
      is calculating the multiplier for all the months together, where as in my table i want the project level multiplier per month to be ccalculated.