Forum Discussion

CiuCiCiao's avatar
CiuCiCiao
Helper I
9 years ago
Solved

Exclude only one value from filtering

Hi Guys,

I have 2 linked tables.

the first has the time in minutes spent for the FTEs for all the services provided:

Time
ServicesmOffice
Service 1                             206Office 3
Service 2                             932Office 1
Service 3                             510Office 2
Service 4                             331Office 1
Service 5                               23Office 2
Service 6                         1.225Office 1
Service 7                               39Office 3
… … …

 

the second table has the total potential of hours worked for each Office:

FTE
OfficePotential Hours
Office 11200
Office 21500
Office 32500

 

My objective is to understand the difference between potential hours and hours really spent on services.

So I got this formula to calculate the effective hours worked:

=
CALCULATE (
    SUM ( Time[m] ),
    FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) )
)
    / 60

Now i have to filter out only Service 6 and I am pretty sure there is a smarter way than this:

 (
    CALCULATE (
        CALCULATE ( SUM ( Time[m] ), Time[Services] = "Service 1" ),
        FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) )
    )
        / 60
)
    + (
        CALCULATE (
            CALCULATE ( SUM ( Time[m] ), Time[Services] = "Service 1" ),
            FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) )
        )
            / 60
    )
...and so on...
Then, when using related function to bring those number on another table, how do I keep Service 6 out of the Job? Should I filter it again manually?

Waiting for your help.

Thanks

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    You could always add a condition to your calculations which could be: Time[Services]<>"Service 6"

     

    This sets a condition for your calculation, which makes sure it always excludes service 6.

     

    If I misunderstand your challenge, please let me know and elaborate.

     

    Best,

    Martin

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    If you first measure is called "EffectiveHoursWorked" you could filter out Service 6 like this:

     

    No6EffectiveHoursWorked = CALCULATE([EffectiveHoursWworked],FILTER(Time,[Services]<>"Service 6"))
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    You could always add a condition to your calculations which could be: Time[Services]<>"Service 6"

     

    This sets a condition for your calculation, which makes sure it always excludes service 6.

     

    If I misunderstand your challenge, please let me know and elaborate.

     

    Best,

    Martin

    • CiuCiCiao's avatar
      CiuCiCiao
      Helper I
      9 years ago

      Guys,

      I you understood and solved my challenge at the same time I feel stupid now though :D

      Greg_Deckler

       

      Thanks

      • CiuCiCiao's avatar
        CiuCiCiao
        Helper I
        9 years ago

        Dear Greg_Deckler Anonymous

        I am back on my problem...

        Now I have gone forward, I have the Operating Cost:

        FTE
        OfficePotential HoursEffective Hours WorkedOperating CostNon Operating Cost
        Office 1                     1.200                                      1.000                    9.400                           10.000
        Office 2                     1.500                                      1.300                    9.800                              7.900
        Office 3                     2.500                                      2.000                 22.000                              2.900

         

        I have to bring them back to the other table:

        Time
        ServicesmOfficeOperating CostNon Operating Cost
        Service 1      206Office 3??
        Service 2      932Office 1……
        Service 3      510Office 2……
        Service 4      331Office 1……
        Service 5         23Office 2……
        Service 6   1.225Office 100
        Service 7         39Office 3……
        … … ………

         

        I have excluded before Service 6 from my calculation, but when bringing it back to the Time table, using the following formula, it will redistribuite also for Service 6.

         

         

        =
RELATED ( FTE[Operating Cost] ) * Time[m]
    / CALCULATE (
        SUM ( Time[m] ),
        FILTER ( Time, Time[Office] = EARLIER ( Time[Office] ) )
    )

         

        I tried different ways but non of them worked out...

        Any help?

        Thanks