3 Replies Latest reply on Aug 16, 2018 8:04 AM by Mahfooj Khan

    Pivot Field Name Filtering - problem with complex calculations

    Alexander Pinzger

      Hi together,

       

      Unfortunately, I have quite a problem regarding my dashboard. It uses Pivot Field Names and Values to create some kind of overview like this:

       

      2018-08-16_12h54_33.png

       

      For different Business Units, I want to track some KPIs. If they are above a certain value, they are green, else they are red.

      It might also be, that a certain rule depends on another KPI. See KPI 5, where it is relevant that "KPI 5 + KPI 4" hits the target. Thus, to create the green bar for KPI 5, KPI 5 and KPI 4 are needed.

      This one works as is should with an EXCLUDE function that sums up both KPIs. My problem is the following:

       

      I need to create a view which is filtered on only one KPI and shows the connected Business Units. For example like this:

       

      2018-08-16_12h58_30.png

       

      Of course, everything is red for KPI 5 because it depends on KPI 4 which is excluded from the view. As noticeable above, all should be green.

       

       

      Does anyone know a workaround or a way to achieve the correct solution for my problem?
      I would really appreciate it since it is a very important task.

      Please note that my real dashboard contains lots of complex functions and stuff and is not that easy structured as the sample workbook I attach. So, a change of the existing formulas would be nearly impossible to manage.
      Hopefully, there is some kind of trick with filtering.

       

       

       

      Thank you very much in advance.

       

      -Alex